Recently learned that mysql-proxy does to issue SET statements to read-only slaves when doing read/write splitting. This create a problem when the client wants to set the character set. The work around is to force all connections to the server to the desired character set which in this case is UTF-8. Add the following lines to the “[mysqld]” section of /etc/my.cnf on the server:
|
1 2 3 |
skip-character-set-client-handshake init-connect='SET NAMES utf8' character-set-server = utf8 |
Here’s my procedure for changing innodb_log_file_size:
Here’s a handy sql command to determine a good log file size:
|
1 |
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G |
then do this:
|
1 |
mysql> select (3838334638 - 3836410803) / 1024 / 1024 as MB_per_min; |
to get the MB per minute. Set the log file size to be large enough to hold one hour of logs.
See http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/ for the full discussion.
By default most MySQL RPM packages do not set the MySQL server to start automatically on boot so this needs to be done manually. On RedHat/CentOS the chkconfig command should be used as follows:
|
1 2 3 4 5 6 7 |
[root@localhost]# /sbin/chkconfig --list mysqld mysqld 0:off 1:off 2:off 3:off 4:off 5:off 6:off [root@localhost]# /sbin/chkconfig --level 345 mysqld on [root@localhost]# /sbin/chkconfig --list mysqld mysqld 0:off 1:off 2:off 3:on 4:on 5:on 6:off |
To simplify administration of MySQL I like to create a /root/.my.cnf file with the mysql root credentials. This way I can run the mysql command line utility without having to authenticate. Just edit /root/.my.cnf and add the following:
|
1 2 3 |
[client] user=root pass=your_mysql_root_pass |
After install MySQL, it’s important that you secure the installation by (a) removing anonymous accounts and (b) setting a password on the admin account. This can be done with mysqladmin or with direct SQL queries. I prefer the SQL queries so that’s what I’ll show here.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[root@localhost]# <strong>mysql mysql</strong> Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17073 Server version: 5.5.27 Distributed by The IUS Community Project mysql> <strong>delete from user where user = "";</strong> Query OK, 2 rows affected (0.00 sec) mysql> <strong>update user set password = PASSWORD('EXnFIT34aU2hXAH2');</strong> Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> <strong>flush privileges;</strong> Query OK, 0 rows affected (0.00 sec) mysql> <strong>quit;</strong> Bye [root@localhost]# |
