create 2nd MySQL instance on Linux
### create directory
mkdir /var/lib/mysql2
chown -R mysql.mysql /var/lib/mysql2/
mkdir /var/log/mysql2
chown -R mysql.mysql /var/log/mysql2
[root@XXX ~]$ vi /etc/my2.cnf
[client]
default-character-set = utf8
port = 3307
socket = /var/lib/mysql2/mysql2.sock
[mysqld]
default-character-set = utf8
port = 3307
socket = /var/lib/mysql2/mysql2.sock
datadir = /var/lib/mysql2
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
max_connections = 1000
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
thread_concurrency = 15
lower_case_table_names = 0
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
skip-name-resolve
wait_timeout=1814400
[mysqldump]
quick
max_allowed_packet = 16M
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
log-error = /var/log/mysqld2.log
pid-file = /var/run/mysqld/mysqld2.pid
### install 2nd db
[root@XXX ~]# mysql_install_db --user=mysql --datadir=/var/lib/mysql2/
### starting 2nd instance of mysql
[root@XXX ~]# mysqld_safe --defaults-file=/etc/my2.cnf &
Starting mysqld daemon with databases from /var/lib/mysql2
### connecting mysql db but using the wrong socket file
[aliao@XXX ~]$ /usr/bin/mysql -u root -p -h localhost -P 3307
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
### connecting mysql db, need to specify the socket file
[aliao@XXX ~]$ /usr/bin/mysql -u root -p -h localhost -P 3307 -S /var/lib/mysql2/mysql2.sock
[aliao@XXX ~]$/usr/bin/mysql -S /var/lib/mysql2/mysql2.sock
mysql> SET PASSWORD FOR root@localhost=PASSWORD('');
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,password from mysql.user;
+-----------+------+----------+
| host | user | password |
+-----------+------+----------+
| localhost | root | |
| XXX | root | |
| 127.0.0.1 | root | |
| localhost | | |
| XXX | | |
+-----------+------+----------+
5 rows in set (0.00 sec)
### shutdown 2nd instance of mysql
[root@XXX ~]# mysqladmin -S /var/lib/mysql2/mysql2.sock shutdown
STOPPING server from pid file /var/run/mysqld/mysqld2.pid
120222 15:49:25 mysqld ended
[1]+ Done mysqld_safe --defaults-file=/etc/my2.cnf
### add 2nd instance of mysql to service list and activate it
[root@XXX init.d]# chkconfig --add mysqld2
[root@XXX init.d]# chkconfig mysqld2 on
[root@XXX init.d]# chkconfig --list|grep mysql
error reading info for service opennms: No such file or directory
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
mysqld2 0:off 1:off 2:on 3:on 4:on 5:on 6:off
Computer Science (10)
