搭建mysql5.7双主结构
-
安装mysql5.7
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-community-common-5.7.25-1.el7.x86_64.rpm wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-community-libs-5.7.25-1.el7.x86_64.rpm wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-community-client-5.7.25-1.el7.x86_64.rpm wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-community-devel-5.7.25-1.el7.x86_64.rpm wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-community-server-5.7.25-1.el7.x86_64.rpm yum install *.rpm --nogpgcheck -
修改配置文件
cd /etc/my.cnf节点1:
[mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/lib/mysql/mysql.sock log-error = /var/log/mysql/error.log port = 3306 basedir = /usr datadir = /mnt/data/mysql character_set_server = utf8 tmpdir = /tmp lc_messages_dir = /usr/share/mysql lc_messages = en_US skip-external-locking # * Fine Tuning # max_connections = 500 max_user_connections = 500 max_connect_errors = 100000 connect_timeout = 1800 wait_timeout = 1800 max_allowed_packet = 16M thread_cache_size = 128 thread_stack = 256k sort_buffer_size = 2M bulk_insert_buffer_size = 8M tmp_table_size = 128M max_heap_table_size = 128M # # * MyISAM # # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched. On error, make copy and try a repair. myisam_recover_options = BACKUP key_buffer_size = 64M open-files-limit = 5000 back_log = 512 table_open_cache = 2048 table_definition_cache = 6144 myisam_sort_buffer_size = 256M concurrent_insert = 2 read_buffer_size = 2M read_rnd_buffer_size = 16M # # * Query Cache Configuration # # Cache only tiny result sets, so we can fit more in the query cache. query_cache_limit = 0K query_cache_size = 0M query_cache_type = OFF # for more write intensive setups, set to DEMAND or OFF # * Logging and Replication general_log_file = /var/lib/mysql/mysql.log general_log = 1 slow_query_log=1 slow_query_log_file = /var/lib/mysql/mysql-slow.log long_query_time = 10 #log_slow_verbosity = query_plan lower_case_table_names = 1 #主节点设置为1 server-id = 1 log-bin=mysql-bin gtid-mode = ON enforce-gtid-consistency = ON #自增gtid起始id auto_increment_offset = 1 #自增gtid步长 auto_increment_increment = 2 #binlog binlog_format = row log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.index expire_logs_days = 7 max_binlog_size = 100M sql_mode = STRICT_TRANS_TABLES # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! default_storage_engine = InnoDB innodb_open_files = 400 innodb_buffer_pool_size = 1526M innodb_log_file_size = 128M innodb_log_buffer_size = 64M innodb_log_files_in_group = 3 innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 30 innodb_max_dirty_pages_pct = 80 innodb_thread_concurrency = 0 innodb_flush_method = O_DIRECT innodb_read_io_threads = 12 innodb_write_io_threads = 12 innodb_io_capacity = 2000 innodb_purge_threads = 2 innodb_purge_batch_size = 64 innodb_old_blocks_pct = 50 transaction_isolation = READ-COMMITTED bind-address=0.0.0.0 [isamchk] key_buffer = 16M节点2：
[mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/lib/mysql/mysql.sock log-error = /var/log/mysql/error.log port = 3306 basedir = /usr datadir = /mnt/data/mysql character_set_server = utf8 tmpdir = /tmp lc_messages_dir = /usr/share/mysql lc_messages = en_US skip-external-locking # * Fine Tuning # max_connections = 500 max_user_connections = 500 max_connect_errors = 100000 connect_timeout = 1800 wait_timeout = 1800 max_allowed_packet = 16M thread_cache_size = 128 thread_stack = 256k sort_buffer_size = 2M bulk_insert_buffer_size = 8M tmp_table_size = 128M max_heap_table_size = 128M # # * MyISAM # # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched. On error, make copy and try a repair. myisam_recover_options = BACKUP key_buffer_size = 64M open-files-limit = 5000 back_log = 512 table_open_cache = 2048 table_definition_cache = 6144 myisam_sort_buffer_size = 256M concurrent_insert = 2 read_buffer_size = 2M read_rnd_buffer_size = 16M # # * Query Cache Configuration # # Cache only tiny result sets, so we can fit more in the query cache. query_cache_limit = 0K query_cache_size = 0M query_cache_type = OFF # for more write intensive setups, set to DEMAND or OFF # * Logging and Replication general_log_file = /var/lib/mysql/mysql.log general_log = 1 slow_query_log=1 slow_query_log_file = /var/lib/mysql/mysql-slow.log long_query_time = 10 #log_slow_verbosity = query_plan lower_case_table_names = 1 #备节点id设置为2 server-id = 2 log-bin = mysql-bin gtid-mode = ON enforce-gtid-consistency = ON #自增gtid起始设置为2，步长为2，这样就不会和master重复 auto_increment_offset = 2 auto_increment_increment = 2 #binlog binlog_format = row log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.index expire_logs_days = 7 max_binlog_size = 100M sql_mode = STRICT_TRANS_TABLES # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! default_storage_engine = InnoDB innodb_open_files = 400 innodb_buffer_pool_size = 1526M innodb_log_file_size = 128M innodb_log_buffer_size = 64M innodb_log_files_in_group = 3 innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 30 innodb_max_dirty_pages_pct = 80 innodb_thread_concurrency = 0 innodb_flush_method = O_DIRECT innodb_read_io_threads = 12 innodb_write_io_threads = 12 innodb_io_capacity = 2000 innodb_purge_threads = 2 innodb_purge_batch_size = 64 innodb_old_blocks_pct = 50 transaction_isolation = READ-COMMITTED bind-address=0.0.0.0 [isamchk] key_buffer = 16M -
初始化mysql
在配置文件中加上skip-grant-tables，跳过权限认证
[mysqld] skip-grant-tables启动mysql
systemctl start mysqld初始化root用户
mysql -u root mysql> set global validate_password_policy=0; mysql> set global validate_password_length=0; mysql> alter user root@'localhost' identified by 'password';把配置文件改回来后重启mysqld
[mysqld] #skip-grant-tables systemctl restart mysqld -
创建同步账号
节点1：
CREATE USER 'repl1'@'10.1.16.11' IDENTIFIED BY 'RepAdmin,.123'; GRANT REPLICATION SLAVE ON *.* TO 'repl1'@'server2_ip'; CHANGE MASTER TO MASTER_HOST = 'server2_ip', MASTER_PORT = 3306, MASTER_USER = 'repl2', MASTER_PASSWORD = 'RepAdmin,.123', # 自动根据gtid进行复制 MASTER_AUTO_POSITION = 1;节点2:
CREATE USER 'repl2'@'10.1.16.12' IDENTIFIED BY 'RepAdmin,.123'; GRANT REPLICATION SLAVE ON *.* TO 'repl2'@'server1_ip'; CHANGE MASTER TO MASTER_HOST = 'server2_ip', MASTER_PORT = 3306, MASTER_USER = 'repl2', MASTER_PASSWORD = 'RepAdmin,.123', # 自动根据gtid进行复制 MASTER_AUTO_POSITION = 1;两个节点账号创建完之后启动复制
start slave查看复制状态，等待master发事件就是复制状态正常了
show slave status
常见问题
-
master的binlog，gtid为空
查看master的gtid集合SHOW GLOBAL VARIABLES LIKE 'gtid_executed';如果确实为空，随便执行一条sql即可，如：
create database test; drop database test; -
slave的gitd比master多
查看两个节点的gtid集合SHOW GLOBAL VARIABLES LIKE 'gtid_executed';如果master为空的，slave有gtid集合，把salve上面的集合清理掉，在slave上
reset master
754
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
