MySQL 多实例配置

 

 

一、             创建数据库存放目录

mkdir /opt/mysql/data1

mkdir /opt/mysql/data2

 

二、             初始化数据库

/opt/mysql/bin

./mysql_install_db --basedir=/opt/mysql --datadir=/opt/mysql/data1 --user=mysql

./mysql_install_db --basedir=/opt/mysql --datadir=/opt/mysql/data2 --user=mysql

 

初始化完成后,请检查data1 data2目录的变化.

 

三、     配置mysql_multi

备份my.cnf文件 cp my.cnf my.cnf.bak   (根据自己my.cnf路径)

vi my.cnf 编辑文件 (注意黑体部分内容)

添加如下内容: 

  1. [mysqld_multi]
  2. mysqld = /opt/mysql/bin/mysqld_safe
  3. mysqladmin = /opt/mysql/bin/mysqladmin
  4. user = mysql
  5.  
  6. [mysqld3306]
  7. port = 3306
  8. socket = /tmp/mysql3306.sock
  9. skip-locking
  10. key_buffer_size = 16K
  11. max_allowed_packet = 1M
  12. table_open_cache = 4
  13. sort_buffer_size = 64K
  14. read_buffer_size = 256K
  15. read_rnd_buffer_size = 256K
  16. net_buffer_length = 2
  17. Kthread_stack = 128K
  18. basedir = /opt/mysql
  19. datadir = /opt/mysql/data
  20. server-id = 1
  21.  
  22. [mysqld3307]
  23. port = 3307
  24. socket = /tmp/mysql3307.sock
  25. skip-locking
  26. key_buffer_size = 16K
  27. max_allowed_packet = 1M
  28. table_open_cache = 4
  29. sort_buffer_size = 64K
  30. read_buffer_size = 256K
  31. read_rnd_buffer_size = 256K
  32. net_buffer_length = 2K
  33. thread_stack = 128K
  34. basedir = /opt/mysql
  35. datadir = /opt/mysql/data1
  36. server-id = 1
  37.  
  38. [mysqld3308]
  39. port = 3308
  40. socket = /tmp/mysql3308.sock
  41. skip-locking
  42. key_buffer_size = 16K
  43. max_allowed_packet = 1M
  44. table_open_cache = 4
  45. sort_buffer_size = 64K
  46. read_buffer_size = 256K
  47. read_rnd_buffer_size = 256K
  48. net_buffer_length = 2K
  49. thread_stack = 128K
  50. basedir = /opt/mysql
  51. datadir = /opt/mysql/data2
  52. server-id = 1


四、     多实例启动

./bin/mysqld_multi --defaults-file=./my.cnf --user=mysql start 3306

./bin/mysqld_multi --defaults-file=./my.cnf --user=mysql start 3307

./bin/mysqld_multi --defaults-file=./my.cnf --user=mysql start 3308

./bin/mysqld_multi --defaults-file=./my.cnf --user=mysql start 3306-3308

 

启动实例有可能报如下错误:

  1

2

3

4

5

WARNING: my_print_defaults command not found.

Please make sure you have this command available and

in your path. The command is available from the latest

MySQL distribution.

ABORT: Can't find command 'my_print_defaults'.

 

解决方法:设置 export PATH=$PATH:/opt/mysql/bin 或者  my_print_defaults /usr/bin 下面设置个软连接.

 

五、     检查MySQL启动状态

netstat -ntlp | grep 33   查看MySQL端口启动状态

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      4788/mysqld        

tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      4635/mysqld        

tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      4583/mysqld

 

ls -l /tmp/*sock*      查看MySQL访问连接

srwxrwxrwx 1 mysql mysql 0 Mar 13 10:26 /tmp/mysql3306.sock

srwxrwxrwx 1 mysql mysql 0 Mar 13 10:26 /tmp/mysql3307.sock

srwxrwxrwx 1 mysql mysql 0 Mar 13 10:26 /tmp/mysql3308.sock

 

ps -ef | grep mysql     查看MySQL实例进程

root      4374     1  0 10:26 pts/0    00:00:00 /bin/sh /opt/mysql/bin/mysqld_safe --port=3307 --socket=/tmp/mysql3307.sock --skip-locking --key_buffer_size=16K --max_allowed_packet=1M --table_open_cache=4 --sort_buffer_size=64K --read_buffer_size=256K --read_rnd_buffer_size=256K --net_buffer_length=2K --thread_stack=128K --basedir=/opt/mysql --datadir=/opt/mysql/data1 --server-id=1

root      4378     1  0 10:26 pts/0    00:00:00 /bin/sh /opt/mysql/bin/mysqld_safe --port=3308 --socket=/tmp/mysql3308.sock --skip-locking --key_buffer_size=16K --max_allowed_packet=1M --table_open_cache=4 --sort_buffer_size=64K --read_buffer_size=256K --read_rnd_buffer_size=256K --net_buffer_length=2K --thread_stack=128K --basedir=/opt/mysql --datadir=/opt/mysql/data2 --server-id=1

mysql     4583  4378  0 10:26 pts/0    00:00:00 /opt/mysql/libexec/mysqld --basedir=/opt/mysql --datadir=/opt/mysql/data2 --user=mysql --skip-locking --key_buffer_size=16K --max_allowed_packet=1M --table_open_cache=4 --sort_buffer_size=64K --read_buffer_size=256K --read_rnd_buffer_size=256K --net_buffer_length=2K --thread_stack=128K --server-id=1 --log-error=/opt/mysql/data2/lgh.com.err --pid-file=/opt/mysql/data2/lgh.com.pid --socket=/tmp/mysql3308.sock --port=3308

mysql     4635  4374  0 10:26 pts/0    00:00:00 /opt/mysql/libexec/mysqld --basedir=/opt/mysql --datadir=/opt/mysql/data1 --user=mysql --skip-locking --key_buffer_size=16K --max_allowed_packet=1M --table_open_cache=4 --sort_buffer_size=64K --read_buffer_size=256K --read_rnd_buffer_size=256K --net_buffer_length=2K --thread_stack=128K --server-id=1 --log-error=/opt/mysql/data1/lgh.com.err --pid-file=/opt/mysql/data1/lgh.com.pid --socket=/tmp/mysql3307.sock --port=3307

root      4662     1  0 10:26 pts/0    00:00:00 /bin/sh /opt/mysql/bin/mysqld_safe --port=3306 --socket=/tmp/mysql3306.sock --skip-locking --key_buffer_size=16K --max_allowed_packet=1M --table_open_cache=4 --sort_buffer_size=64K --read_buffer_size=256K --read_rnd_buffer_size=256K --net_buffer_length=2K --thread_stack=128K --basedir=/opt/mysql --datadir=/opt/mysql/data --server-id=1

mysql     4788  4662  0 10:26 pts/0    00:00:00 /opt/mysql/libexec/mysqld --basedir=/opt/mysql --datadir=/opt/mysql/data --user=mysql --skip-locking --key_buffer_size=16K --max_allowed_packet=1M --table_open_cache=4 --sort_buffer_size=64K --read_buffer_size=256K --read_rnd_buffer_size=256K --net_buffer_length=2K --thread_stack=128K --server-id=1 --log-error=/opt/mysql/data/lgh.com.err --pid-file=/opt/mysql/data/lgh.com.pid --socket=/tmp/mysql3306.sock --port=3306

root     13469  3226  0 10:29 pts/0    00:00:00 grep mysql

 

六、     MySQL连接测试:

mysql -S /tmp/mysql3306.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.1.52hkrt Source distribution

 

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql>show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| test               |

+--------------------+

3 rows in set (0.00 sec)

 

mysql>exit

Bye

 

七、     关闭MySQL多实例:

mysqld_multi --defaults-file=/opt/mysql/my.cnf --user=root stop 3306

mysqld_multi --defaults-file=/opt/mysql/my.cnf --user=root stop 3307

mysqld_multi --defaults-file=/opt/mysql/my.cnf --user=root stop 3308

或:

mysqld_multi --defaults-file=/opt/mysql/my.cnf --user=root stop 3306-3308