MYSQL 多实例安装以及常见运维点

重点：配置GNR时需要所有的GNR以[ mysqld xxx]的格式命名，否则会报找到GNR的错误 准备Mysqld_multi配置文件加入多实例的配置信息 指定GNR启动实例(不要指定MYSQLD这个关键词) mysqld_multi start 3308 指定端口和SOCK文件关闭实例 mysqladmin -uroot -p123456 -P3307 -S/mydata/data2/mysql.sock shutdown 指定连接实例 mysql -uroot -p123456 -S/mydata/data2/mysql.sock 指定启动实例 mysqld_multi start mysqld3307 如下为多实例的配置文件 [client] #socket = /mydata/data/mysql.sock [mysqld_multi] log=/mydata/mysqld_multi.log #mysqld=/usr/sbin/mysqld user=mysql #basedir = /usr #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld3306] ########basic settings######## user=mysql server-id = 333 port = 3306 socket=/mydata/data/master1.sock #skip-grant-tables character_set_server=utf8 skip_name_resolve = 1 max_connections = 2000 max_connect_errors = 1000 datadir = /mydata/data explicit_defaults_for_timestamp = 1 join_buffer_size = 134217728 tmp_table_size = 67108864 tmpdir = /tmp lower_case_table_names=1 log_error = error.log ########replication settings######## master_info_repository = TABLE relay_log_info_repository = TABLE log_bin = bin.log gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates binlog_format = row relay_log = relay.log relay_log_recovery = 1 #binlog_gtid_simple_recovery = 1 slave_skip_errors = ddl_exist_errors ########innodb settings######## innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 8 innodb_lock_wait_timeout = 50 innodb_log_file_size = 500M innodb_purge_threads = 4 innodb_large_prefix = 1 innodb_thread_concurrency = 64 innodb_print_all_deadlocks = 1 innodb_strict_mode = 1 innodb_sort_buffer_size = 67108864 ########semi sync replication settings######## #plugin_dir=/usr/local/mysql/lib/plugin #plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled = 1 loose_rpl_semi_sync_slave_enabled = 1 loose_rpl_semi_sync_master_timeout = 5000 [mysqld3307] ########basic settings######## user=mysql server-id = 789 port = 3307 skip-grant-tables socket=/mydata/data2/master2.sock character_set_server=utf8 skip_name_resolve = 1 max_connections = 2000 max_connect_errors = 1000 datadir = /mydata/data2 explicit_defaults_for_timestamp = 1 join_buffer_size = 134217728 tmp_table_size = 67108864 tmpdir = /tmp lower_case_table_names=1 log_error = error.log ########replication settings######## master_info_repository = TABLE relay_log_info_repository = TABLE log_bin = bin.log gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates binlog_format = row relay_log = relay.log relay_log_recovery = 1 #binlog_gtid_simple_recovery = 1 slave_skip_errors = ddl_exist_errors ########innodb settings######## innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 8 innodb_lock_wait_timeout = 50 innodb_log_file_size = 500M innodb_purge_threads = 4 innodb_large_prefix = 1 innodb_thread_concurrency = 64 innodb_print_all_deadlocks = 1 innodb_strict_mode = 1 innodb_sort_buffer_size = 67108864 ########semi sync replication settings######## #plugin_dir=/usr/local/mysql/lib/plugin #plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled = 1 loose_rpl_semi_sync_slave_enabled = 1 loose_rpl_semi_sync_master_timeout = 5000