189 8069 5689
一、MySQL多实例部署
网站建设公司,为您提供网站建设,网站制作,网页设计及定制网站建设服务,专注于成都企业网站建设,高端网页制作,对电动窗帘等多个行业拥有丰富的网站建设经验的网站建设公司。专业网站设计,网站优化推广哪家好,专业成都网站推广优化,H5建站,响应式网站。
版本：5.7.18
1.软件安装
# tar xf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local
# cd /usr/local
# chown -R root.root mysql-5.7.18-linux-glibc2.5-x86_64
# ln -sv mysql-5.7.18-linux-glibc2.5-x86_64 mysql5.7.18
# mkdir /data/{mydata3307,mydata3308}
# chown -R mysql.mysql /data/mydata33*2.提供多实例服务启动脚本
# cd /usr/local/mysql5.7.18 # cp support-files/mysqld_multi.server /etc/init.d/mysqld_multi # chmod +x /etc/init.d/mysqld_multi # chkconfig --add mysqld_multi # vi /etc/init.d/mysqld_multi export PATH=$PATH:/usr/local/mysql5.7.18/bin basedir=/usr/local/mysql5.7.18 bindir=/usr/local/mysql5.7.18/bin
3.提供配置文件
# cat /etc/my.cnf [mysql] #password = 123456 #prompt = [\\u@\\h][\\d]>\\_ socket = /tmp/mysql5.7.18.sock [client] #password = 123456 #prompt = [\\u@\\h][\\d]>\\_ socket = /tmp/mysql5.7.18.sock [mysqld_multi] mysqld = /usr/local/mysql5.7.18/bin/mysqld_safe mysqladmin =/usr/local/mysql5.7.18/bin/mysqladmin log =/data/mydata3307/mysqld_multi.log # 每个实例都设置统一管理密码，方便使用服务脚本停止实例 user = root pass = 123456 #初始化需要[mysqld]段配置，否则初始化的时候加载不到[mysqld3307]及[mysqld3308]段中关于设置独立undo表空间及共享表空间大小 [mysqld] innodb_buffer_pool_size = 4096M #innodb_buffer_pool_size = 16384M innodb_undo_log_truncate=ON innodb_undo_tablespaces = 2 innodb_data_file_path=ibdata1:1G:autoextend [mysqld3307] innodb_buffer_pool_size = 4096M #innodb_buffer_pool_size = 16384M port = 3307 socket = /data/mydata3307/mysql5.7.18.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 10M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 16M thread_cache_size = 4 max_connections=1500 character_set_server=utf8 group_concat_max_len=65535 log_bin_trust_function_creators=1 log_queries_not_using_indexes = ON log_throttle_queries_not_using_indexes = 2 interactive_timeout = 600 wait_timeout = 600 connect_timeout = 10 expire_logs_days = 30 replicate-ignore-db=information_schema replicate-ignore-db=performance_schema replicate-ignore-db=mysql replicate-ignore-db=sys log_timestamps=SYSTEM innodb_print_all_deadlocks=1 basedir=/usr/local/mysql5.7.18 datadir=/data/mydata3307 innodb_undo_log_truncate=ON innodb_undo_tablespaces = 2 innodb_data_file_path=ibdata1:1G:autoextend core_file sync_binlog = 0 innodb_flush_log_at_trx_commit = 2 ##Master #log-bin=mysql-bin log-bin=/data/mydata3307/mysql-bin #binlog_format=mixed binlog_format=row server-id=3307 lower_case_table_names = 1 skip-name-resolve innodb_file_per_table=1 long_query_time=2 slow_query_log=1 slow_query_log_file=/data/mydata3307/slow-query.log sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' #slave slave-parallel-type=LOGICAL_CLOCK #slave-parallel-workers=16 slave-parallel-workers=4 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON slave_preserve_commit_order=1 log-slave-updates=true slave_skip_errors='1032,1062' relay_log=/data/mydata3307/localhost-relay-bin ####gtid###### gtid_mode = ON enforce_gtid_consistency = ON master_verify_checksum = 1 slave_sql_verify_checksum = 1 [mysqld3308] innodb_buffer_pool_size = 2048M #innodb_buffer_pool_size = 16384M port = 3308 socket = /data/mydata3308/mysql5.7.18.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 10M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 16M thread_cache_size = 4 max_connections=1500 character_set_server=utf8 group_concat_max_len=65535 log_bin_trust_function_creators=1 log_queries_not_using_indexes = ON log_throttle_queries_not_using_indexes = 2 interactive_timeout = 600 wait_timeout = 600 connect_timeout = 10 expire_logs_days = 30 replicate-ignore-db=information_schema replicate-ignore-db=performance_schema replicate-ignore-db=mysql replicate-ignore-db=sys log_timestamps=SYSTEM innodb_print_all_deadlocks=1 basedir=/usr/local/mysql5.7.18 datadir=/data/mydata3308 innodb_undo_log_truncate=ON innodb_undo_tablespaces = 2 innodb_data_file_path=ibdata1:1G:autoextend core_file sync_binlog = 0 innodb_flush_log_at_trx_commit = 2 ##Master #log-bin=mysql-bin log-bin=/data/mydata3308/mysql-bin #binlog_format=mixed binlog_format=row server-id=3308 lower_case_table_names = 1 skip-name-resolve innodb_file_per_table=1 long_query_time=2 slow_query_log=1 slow_query_log_file=/data/mydata3308/slow-query.log sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' #slave slave-parallel-type=LOGICAL_CLOCK #slave-parallel-workers=16 slave-parallel-workers=4 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON slave_preserve_commit_order=1 log-slave-updates=true slave_skip_errors='1032,1062' relay_log=/data/mydata3308/localhost-relay-bin ####gtid###### gtid_mode = ON enforce_gtid_consistency = ON master_verify_checksum = 1 slave_sql_verify_checksum = 1
4.初始化实例
实例3307 # cd /usr/local/mysql5.7.18/bin # ./mysqld --user=mysql --basedir=/usr/local/mysql5.7.18/ --datadir=/data/mydata3307/ --initialize --initialize-insecure 实例3308 # ./mysqld --user=mysql --basedir=/usr/local/mysql5.7.18/ --datadir=/data/mydata3308/ --initialize --initialize-insecure
5.启动服务
# service mysqld_multi start
# service mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
# ss -ntpl | grep mysqld
LISTEN 0 128 :::3307 :::* users:(("mysqld",8004,29))
LISTEN 0 128 :::3308 :::* users:(("mysqld",8003,29))
多实例启动成功6.设置管理账号密码
默认初始化密码为空，提示输入密码时，直接回车 # mysqladmin -uroot -p password 123456 -S /data/mydata3307/mysql5.7.18.sock # mysqladmin -uroot -p password 123456 -S /data/mydata3308/mysql5.7.18.sock
7.停止实例
# service mysqld_multi stop 3307 # service mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld3307 is not running MySQL server from group: mysqld3308 is running
注意：
多实例服务启动脚本启动报错
[root@localhost mysql5.7.18]# service mysqld_multi start
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'.
This command is available from the latest MySQL
distribution. Please make sure you have the command
in your PATH.
修改/etc/init.d/mysqld_multi
export PATH=$PATH:/usr/local/mysql5.7.18/bin