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 4 5 |
[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. Start mysql command line utility and select the mysql database. Remove anonymous accounts. Set a password on the mysql root account. Flush privileges.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[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]# |
1. Create the tmp directory:
|
1 |
mkdir /var/lib/mysqltmp |
2. Set permissions:
|
1 |
chown mysql:mysql /var/lib/mysqltmp |
3. Determine mysql user id:
|
1 |
id mysql |
4. Edit /etc/fstab and add the following line:
|
1 |
tmpfs /var/lib/mysqltmp tmpfs rw,gid=27,uid=27,size=256M,nr_inodes=10k,mode=0700 0 0 |
5. Mount the new tmpfs partition:
|
1 |
mount /var/lib/mysqltmp |
6. Edit /etc/my.cnf and set location of tmp directory:
|
1 |
tmpdir=/var/lib/mysqltmp |
7. Restart mysqd
|
1 |
/sbin/service mysqld restart |
To setup an alternate instance of MySQL listening on TCP port 3307 on a RHEL5 server follow these steps. 1. Setup a new MySQL config file.
|
1 2 |
cp /etc/my.cnf /etc/my-3307.cnf |
Add a line like this:
|
1 |
port = 3307 |
to the “[mysqld]” section. Edit /etc/my-3307.cnf and change:
|
1 2 |
datadir=/var/lib/mysql |
to
|
1 2 |
datadir=/var/lib/mysql-3307 |
Change:
|
1 2 3 |
socket=/var/lib/mysql/mysql.sock |
to:
|
1 2 3 |
socket=/var/lib/mysql-3307/mysql.sock |
Change the following lines:
|
1 2 3 4 5 6 7 8 9 |
log-slow-queries=/var/lib/mysqllogs/slow-log log-bin=/var/lib/mysqllogs/bin-log log-bin-index=/var/lib/mysqllogs/bin-log.index relay-log=/var/lib/mysqllogs/relay-log relay-log-index=/var/lib/mysqllogs/relay-log.index log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid |
to:
|
1 2 3 4 5 6 7 8 9 |
log-slow-queries=/var/lib/mysqllogs-3307/slow-log log-bin=/var/lib/mysqllogs-3307/bin-log log-bin-index=/var/lib/mysqllogs-3307/bin-log.index relay-log=/var/lib/mysqllogs-3307/relay-log relay-log-index=/var/lib/mysqllogs-3307/relay-log.index log-error=/var/log/mysqld-3307.log pid-file=/var/run/mysqld/mysqld-3307.pid |
Change server-id to a unique value:
|
1 2 3 |
server-id=2 |
2. Setup a new service control script.
|
1 2 3 |
cp /etc/init.d/mysqld /etc/init.d/mysqld-3307 |
Edit /etc/init.d/mysqld-3307 and add this line:
|
1 2 3 |
MYCNF=/etc/my-3307.cnf |
directly after:
|
1 2 3 4 |
# Source networking configuration. . /etc/sysconfig/network |
so that you have:
|
1 2 3 4 5 6 |
# Source networking configuration. . /etc/sysconfig/network MYCNF=/etc/my-3307.cnf |
Next change this function:
|
1 2 3 4 5 6 7 8 9 |
get_mysql_option(){ result=`/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1` if [ -z "$result" ]; then # not found, use default result="$3" fi } |
to:
|
1 2 3 4 5 6 7 8 9 |
get_mysql_option(){ result=`/usr/bin/my_print_defaults -c $MYCNF "$1" | sed -n "s/^--$2=//p" | tail -n 1` if [ -z "$result" ]; then # not found, use default result="$3" fi } |
Notice that the change is to add “-c $MYCNF” to the call to “my_print_defaults”. Finally run the following search/replace commands to fixup the program name, add defaults file to mysqld_safe call and set unique pid and subsys files:
|
1 2 3 4 5 6 7 |
replace 'prog="MySQL"' 'prog="MySQL-3307"' -- /etc/init.d/mysqld-3307 replace '/usr/bin/mysqld_safe' '/usr/bin/mysqld_safe --defaults-file=$MYCNF' \ -- /etc/init.d/mysqld-3307 replace 'mysqld.pid' 'mysqld-3307.pid' -- /etc/init.d/mysqld-3307 replace '/var/lock/subsys/mysqld' '/var/lock/subsys/mysqld-3307' -- /etc/init.d/mysqld-3307 |
3. Setup directories
|
1 2 3 4 |
mkdir /var/lib/mysql-3307 /var/lib/mysqllogs-3307 chown mysql.mysql /var/lib/mysql-3307/ /var/lib/mysqllogs-3307 chmod o-rwx /var/lib/mysqllogs-3307 |
4. Set service to start on boot
|
1 2 3 |
/sbin/chkconfig mysqld-3307 on |
5. Start the new instance:
|
1 2 3 |
/sbin/service mysqld-3307 start |
On
Came across a handy configuration setting for mysql:
|
1 |
<em>expire_logs_days = 7</em> |
Controls how many days of binary log files should be retained. Very good way to keep the binary logs from over flowing available storage.
Reliable Penguin provides managed web hosting, systems administration, website and server migrations, and expert consulting.
Submit the form—or for immediate service call 866-649-7984.