一台主机部署多个mysqld实例配置

配置选项说明

mysqld_multi：配置mysqld_multi命令参数信息
client：客户端参数信息
mysqld：每个实例配置的参数
mysql：命令行工具mysql配置参数
mysqld_safe：mysqld启动的配置参数
mysqldump：数据备份时参数
myisamchk：myisamchk命令配置参数

编辑配置文件my.cnf

# vim /etc/my.cnf
 [mysqld_multi]
 mysqld=/usr/local/mysql-5.5.14/bin/mysqld_safe
 mysqladmin=/usr/local/mysql-5.5.14/bin/mysqladmin
 user=admin
 password=mysqladmin
 log=/var/log/multi.log
[client]
[mysqld3306]
 user            = mysql
 port            = 3306
 socket          = /data/mydata3306/mysql.sock
 pid-file        = /data/mydata3306/mysql.pid
 datadir         = /data/mydata3306
 skip-external-locking
 skip-name-resolve
 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       = 3306
 log-bin=mysql-bin-3306
 log-bin-index=mysql-bin-3306.index
 binlog_format=row
[mysqld3307]
 user            = mysql
 port            = 3307
 socket          = /data/mydata3307/mysql.sock
 pid-file        = /data/mydata3307/mysql.pid
 datadir         = /data/mydata3307
 tmpdir          = /data/mydata3307
 skip_external_locking
 skip-name-resolve
 back_log        = 100
 default-storage-engine = INNODB
 collation = utf8_general_ci
 max_connections = 800
 max_connect_errors = 100000
 interactive_timeout = 172800
 connect_timeout = 10
 max_allowed_packet = 4M
 max_heap_table_size = 128M
 tmp_table_size = 128M
 max_length_for_sort_data = 4096
 net_buffer_length = 8K
 sort_buffer_size = 8M
 join_buffer_size = 1M
 read_buffer_size = 1M
 read_rnd_buffer_size = 2M
 table_cache = 1024
 thread_cache_size = 64
 thread_concurrency = 8
 query_cache_type = 0
 query_cache_size = 64M
 query_cache_limit = 1M
 log-error = /data/mydata3307/error.log
 log_warnings
 long_query_time = 1
 slow_query_log
 slow_query_log_file = /data/mydata3307/slow-query.log
 #log_slow_queries = /data/mysqldata3307/log/slow-query.log
 log_queries_not_using_indexes
 binlog_cache_size = 8M
 max_binlog_size = 512M
 log-bin = /data/mydata3307/mysql-bin-3307
 log-bin-index = /data/mydata3307/mysql-bin-3307.index
 expire_logs_days = 3
 server-id               = 3307
 key_buffer_size = 32M
 bulk_insert_buffer_size = 16M
 myisam_sort_buffer_size = 64M
 myisam_max_sort_file_size = 10G
 myisam_repair_threads = 1
 myisam_recover
 innodb_file_per_table
 innodb_flush_method=O_DIRECT
[mysqldump]
 quick
 max_allowed_packet = 1G
[mysqld_safe]
 open-files-limit = 8192
[mysql]
 no-auto-rehash
[myisamchk]
 key_buffer_size = 8M
 sort_buffer_size = 8M
[mysqlhotcopy]
 interactive-timeout

 文章源自运维生存时间-https://www.ttlsa.com/mysql/single-server-multi-mysql-instance/

初始化多库实例

# ./scripts/mysql_install_db --datadir=/data/mydata3306 --user=mysql // 初始化3306
 # ./scripts/mysql_install_db --datadir=/data/mydata3307 --user=mysql // 初始化3307

多实例命令

# ./bin/mysqld_multi start 3306,3307 //启动
 # ./bin/mysqld_multi report 3306,3307 // 查看实例状态
 Reporting MySQL servers
 MySQL server from group: mysqld3306 is running
 MySQL server from group: mysqld3307 is running

赋权限

# mysql -uroot -P3306 -S /data/mydata3306/mysql.sock
>grant shutdown on *.* to 'admin'@'localhost' identified by 'mysqladmin';
# mysql -uroot -P3307 -S /data/mydata3307/mysql.sock
>grant shutdown on *.* to 'admin'@'localhost' identified by 'mysqladmin';

 参考

更多MySQL文章：https://www.ttlsa.com/mysql文章源自运维生存时间-https://www.ttlsa.com/mysql/single-server-multi-mysql-instance/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/single-server-multi-mysql-instance/