需求：实现主从复制，且可以向从服务器写数据。
一、主服务器配置 1、启用binlog并设置服务器ID
编辑主服务器的配置文件 /etc/my.cnf（宝塔默认），添加如下内容：
1 2 3 4 5 [mysqld] #不带路径则与数据库所在路径一致 log-bin=/www/server/data/mysql-bin #服务器编号，在主从架构中必须唯一 server-id=1
如果省略server-id（或将其显式设置为默认值0），则主服务器拒绝来自从服务器的任何连接。
2、设置binlog存放路径/www/server/data可写权限
1 2 mkdir /www/server/data chown mysql.mysql /www/server/data
宝塔默认存放在/www/server/data并设置可写权限，此步骤可跳过。
3、其他设置 主服务器的 my.cnf 文件中使用以下配置项：
1 2 innodb_flush_log_at_trx_commit = 1 sync_binlog = 100
注意：sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。https://www.cnblogs.com/xuxubaobao/p/10839979.html
4、重启MySql
1 /etc/init.d/mysqld restart
5、检查skip_networking是否为OFF 主服务器上 skip_networking 选项处于 OFF 关闭状态, 这是默认值。ON的，则从服务器无法与主服务器通信，并且复制失败。
1 2 3 4 5 6 7 mysql> show variables like '%skip_networking%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | skip_networking | OFF | +-----------------+-------+ 1 row in set (0.00 sec)
6、创建一个专门用于复制数据的用户 从服务器需要使用Mysql 主服务器上的用户名和密码连接到主服务器。simon 可以从任何主机上连接到主服务器上进行复制操作, 并且用户 simon 仅可以使用复制的权限。
1 2 3 4 5 6 #创建用户 CREATE USER 'simon'@'%' #开通Repl_slave_priv: Y权限 grant replication slave on *.* to 'simon'@'%' identified by '2019Simon'; show grants for 'simon'@'%'; #revoke all on *.* from 'simon'@'%'; #移除权限
为了验证创建用户是否成功可用，我们在从服务器上尝试一下登录：
1 mysql -h115.29.*.108 -usimon -p2019Simon
如果可以正常登录，说明成功可用。如果不行，请检查一下防火墙安全规则，开放3306端口。
二、备份数据 情况一：主服务器中已经存在数据，而且数据量不大
1、使用mysqldump命令从主库导出数据，并写入二进制日志
1 2 cd ~ mysqldump -uroot -pee5ff2292e649127 --single-transaction --master-data --databases db_ssp > db_ssp_bak.sql
--master-data参数，锁定所有表并写入二进制日志，个人理解：是告诉从服务器从刚刚导出数据的哪个点开始同步数据。--ignore-table=db_ssp.ssp_ads_fail_log --ignore-table=db_ssp.ssp_channel_flow_collect--single-transaction参数，以上只针对某一个数据库，如果是所有数据库，则执行：
1 2 cd ~ mysqldump -uroot -pee5ff2292e649127 --all-databases --master-data=1 > all.sql
2、在主服务器中使用rsync工具，把备份出来的数据传输到从服务器中。
1 rsync -P --rsh=ssh db_ssp_bak20191122.sql 47.103.38.242:/root/
3、导入从服务器数据库
1 2 mysql> use db_ssp; mysql> source /root/db_ssp_bak20191122.sql;
情况二：主服务器中已经存在数据，而且数据量很大
情况三：主服务器中无数据
三、从服务器配置 1、启用binlog并设置服务器ID
1 2 3 4 5 [mysqld] #不带路径则与数据库所在路径一致 log-bin=/www/server/data/mysql-bin #服务器编号，在主从架构中必须唯一 server-id=2
2、配置要复制的和忽略的数据库
1 2 3 4 5 6 7 8 9 10 11 replicate_do_db=db_ssp #replicate_do_db=db_ssp2 #如果复制多个库，不用逗号隔开，而是一行一个 replicate_ignore_db=information_schema #忽略复制mysql5.7默认的4个库 replicate_ignore_db=performance_schema replicate_ignore_db=mysql replicate_ignore_db=sys replicate_ignore_table=db_ssp.ssp_admin_user #忽略复制db_ssp库中的几张表 replicate_ignore_table=db_ssp.ssp_ads_access_count replicate_ignore_table=db_ssp.ssp_ads_fail_log replicate_ignore_table=db_ssp.ssp_channel_flow_collect replicate_ignore_table=db_ssp.ssp_stat_income_display
replicate_do_db为白名单，表示同步哪几个数据库，注意：在my.cnf中设置多个数据库时，不能使用逗号隔开，需要一行一个。如果只复制指定的表，使用replicate_do_table=表名；replicate_ignore_db为黑名单，表示不同步哪几个数据库。如果不复制指定的表replicate_ignore_table=库名.表名。从服务器上设置。主服务器上设置：
1 2 binlog_do_db=db_ssp binlog_do_db=db_ssp2
但过滤功能会造成一定的负载，推荐放在从服务器上配置。
四、启动主从复制 1、进入从服务器，登录Mysql，设置要复制的主服务器信息
1 mysql> change master to MASTER_HOST='115.29.*.108', MASTER_USER='simon', MASTER_PASSWORD='2019Simon';
分别输入主服务器的IP、上面创建的专用于复制的用户名和密码，回车。
2、启动从服务器的复制线程
3、检查是否成功
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 115.29.191.108 //主库IP Master_User: simon //复制用户名(连接主库的用户) Master_Port: 3306 //主库的端口 Connect_Retry: 60 //断链之后重试次数 Master_Log_File: mysql-bin.000004 //已经获取到binlog的文件名 Read_Master_Log_Pos: 428027692 //已经获取到的binlog位置号 Relay_Log_File: iZuf61lyoku3o73ozrd7x1Z-relay-bin.000004 //从库已经运行过的relaylog的文件名 Relay_Log_Pos: 252264496 //从库已经运行过的relay的位置号 Relay_Master_Log_File: mysql-bin.000004 //主库当前最新binlog文件名 Slave_IO_Running: Yes //从库IO线程状态 Slave_SQL_Running: Yes //从库SQL线程状态 Replicate_Do_DB: db_ssp Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 428027544 Relay_Log_Space: 428028296 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: a3d29300-efeb-11e9-830b-00163e0ac428 Master_Info_File: /www/server/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
从以上结果中，我们可以看到Slave_IO_Running: Yes（ I/O 线程）、 YESSlave_SQL_Running: Yes（SQL线程）都是yes，表示主从复制搭建成功！
五、常见问题 1、MySQL主从复制故障，报1007错误的解决办法show slave status我们发现如下错误：
1 2 Last_SQL_Errno: 1007 Last_SQL_Error: Error 'Can't create database 'db_ssp'; database exists' on query. Default database: 'db_ssp'. Query: 'create database `db_ssp` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci'
答：
1 2 3 stop slave; set global sql_slave_skip_counter=1; start slave;
解决方法二：根据可以忽略的错误号修改从服务器的配置文件
1 2 vim /etc/my.cnf slave-skip-errors=1007,1008,1032,1062
参考：https://www.2cto.com/database/201807/763757.html
2、MySQL主从复制故障，报1062错误的解决办法从服务器的Mysql，执行show slave status\G;，发现如下错误提示：
1 2 3 4 5 6 Slave_IO_Running: Yes Slave_SQL_Running: No Last_Errno: 1062 Last_Error: Error 'Duplicate entry '87' for key 'PRIMARY'' on query. Default database: 'db_ssp'. Query: 'INSERT INTO `ssp_stat_income_display` (`ad_id`, `channel_id`, `show_num`, `click_num`, `date`, `media_id`) VALUES (10, 5, 240, 18, 20191123, 4)' Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '87' for key 'PRIMARY'' on query. Default database: 'db_ssp'. Query: 'INSERT INTO `ssp_stat_income_display` (`ad_id`, `channel_id`, `show_num`, `click_num`, `date`, `media_id`) VALUES (10, 5, 240, 18, 20191123, 4)'
从以上反馈中可以得知，Slave_IO线程正常，即从主服务器上同步Relay log（中断日志）是正常的，Slave_SQL线程处于NO状态，即从服务器从Relay log（中断日志）中执行sql命令失败。从服务器（测试环境）上做了写入操作，导致Slave_SQL线程执行命令失败，主从同步中断。
1 2 3 4 5 6 stop slave; change master to MASTER_HOST='115.29.*.108', MASTER_USER='simon', MASTER_PASSWORD='2019Simon'; start slave; #发现再次出现了上面第1个问题的错误 stop slave; set global sql_slave_skip_counter=1; start slave;
参数sql_slave_skip_counter跳过的是event,不是单个事务，参考：https://blog.csdn.net/weixin_34236869/article/details/90251043
3、从服务器的my.cnf添加replicate_lgnore_table后，启动不了，提示如下：
1 2 3 [....] Starting mysqld (via systemctl): mysqld.serviceJob for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details. failed!
原因是：从库已经存在之前的relay log，解决办法同第4个问题
4、从服务器Mysql执行start slave后报错ERROR1872，提示如下：
1 ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
原因是：从库已经存在之前的relay log，解决办法：使用RESET SLAVE语句，清除master信息和relay日志的信息，删除所有的relay日志文件，并开始创建一个全新的中继日志
1 2 mysql> stop slave; mysql> reset slave;
5、在不影响主从复制的情况下，假设我们需要在主服务器上执行一些SQL语句，但又不想同步到从服务器，怎么办？SET sql_log_bin=0，本次连接mysql的session里面所输入并执行的语句都不会被计入bin_log，也不会被从服务器执行。执行SET sql_log_bin=1又可以恢复计入bin_log，很有用，强调一下：SET sql_log_bin=0这个设置只对当前这次连接有效，不影响其他操作计入bin_log。navicat上修改，一边在mysql命令窗口修改，观察主从变化情况。
更多故障排查，参考：https://www.jianshu.com/p/ecd00306cf92
六、附录 主服务器my.cnf
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 [client] #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock datadir = /www/server/data default_storage_engine = InnoDB performance_schema_max_table_instances = 400 table_definition_cache = 400 skip-external-locking key_buffer_size = 64M max_allowed_packet = 100G table_open_cache = 256 sort_buffer_size = 1M net_buffer_length = 4K read_buffer_size = 1M read_rnd_buffer_size = 256K myisam_sort_buffer_size = 16M thread_cache_size = 32 query_cache_size = 32M tmp_table_size = 64M sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES explicit_defaults_for_timestamp = true #skip-name-resolve max_connections = 500 max_connect_errors = 100 open_files_limit = 65535 log-bin=mysql-bin binlog_format=mixed server-id = 1 expire_logs_days = 10 slow_query_log=1 slow-query-log-file=/www/server/data/mysql-slow.log long_query_time=0.1 log_queries_not_using_indexes=on early-plugin-load = "" innodb_data_home_dir = /www/server/data innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /www/server/data innodb_buffer_pool_size = 256M innodb_log_file_size = 128M innodb_log_buffer_size = 32M innodb_flush_log_at_trx_commit = 1 sync_binlog = 0 innodb_lock_wait_timeout = 50 innodb_max_dirty_pages_pct = 90 innodb_read_io_threads = 2 innodb_write_io_threads = 2 [mysqldump] user=root password="ee5ff2292e649***" quick max_allowed_packet = 500M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 64M sort_buffer_size = 1M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
从服务器my.cnf
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 [client] #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock datadir = /www/server/data default_storage_engine = InnoDB performance_schema_max_table_instances = 400 table_definition_cache = 400 skip-external-locking key_buffer_size = 128M max_allowed_packet = 100G table_open_cache = 512 sort_buffer_size = 2M net_buffer_length = 4K read_buffer_size = 2M read_rnd_buffer_size = 256K myisam_sort_buffer_size = 32M thread_cache_size = 64 query_cache_size = 64M tmp_table_size = 64M sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES explicit_defaults_for_timestamp = true #skip-name-resolve max_connections = 500 max_connect_errors = 100 open_files_limit = 65535 log-bin=mysql-bin binlog_format=mixed server-id = 2 expire_logs_days = 10 slow_query_log=1 slow-query-log-file=/www/server/data/mysql-slow.log long_query_time=0.1 log_queries_not_using_indexes=on early-plugin-load = "" #自定义================================================ replicate_do_db=db_ssp replicate_ignore_db=information_schema replicate_ignore_db=performance_schema replicate_ignore_db=mysql replicate_ignore_db=sys replicate_ignore_table=db_ssp.ssp_admin_user replicate_ignore_table=db_ssp.ssp_ads_access_count replicate_ignore_table=db_ssp.ssp_ads_fail_log replicate_ignore_table=db_ssp.ssp_channel_flow_collect replicate_ignore_table=db_ssp.ssp_stat_income_display slave-skip-errors=1007,1008,1032,1062 #================================================ innodb_data_home_dir = /www/server/data innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /www/server/data innodb_buffer_pool_size = 512M innodb_log_file_size = 256M innodb_log_buffer_size = 64M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 innodb_max_dirty_pages_pct = 90 innodb_read_io_threads = 2 innodb_write_io_threads = 2 [mysqldump] quick max_allowed_packet = 500M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 2M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
七、心得 1、在主从复制架构中，为了避免从服务器复制因为人为写原因导致中断，可以将库设置为只读：mysql> set global read_only=1;（也可以将从服务器的 my.cnf 设置 read-only=1）从服务器上的数据库就被设置成全局只读了，但是如果只针对某一个数据库只读，可以创建一个只读用户（只能select）即可。mysql> set global read_only=0;https://blog.csdn.net/u013636377/article/details/50905768
参考：https://www.jianshu.com/p/faf0127f1cb2 https://blog.csdn.net/z69183787/article/details/70183284 https://www.jianshu.com/p/ecd00306cf92 https://www.imooc.com/learn/539 MySQL主从复制教程https://blog.csdn.net/tr1912/article/details/81302648 双机热备，即主主复制，看懂了，哈哈https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#option_mysqld_replicate-ignore-table 官方主从复制参数详解
