英文好的可以直接去mysql官网查看 https://dev.mysql.com/doc/refman/5.7/en/replication.html
1 环境准备
我这里准备两套linux虚拟机，主mysql服务器,从mysql 服务区
ip 192.168.1.30
ip 192.168.1.100
1.1 备份主mysql数据库
1.2 从数据库恢复主mysql数据库
1.3 配置主mysql服务器
配置server-id
[mysqld]
# server-id要唯一，这里取ip段最后一段
server-id = 30
#开启mysql binlog功能
log-bin = mysql-bin
#同步全部库
binlog_do_db = '*';
# 下来配置按需配置
# binlog忽略的数据库
# binlog-ignore-db = mysql
# binlog需要同步的数据库 ————同步全部数据库，就不写下面库名称
# binlog-do-db = jxbp_user
# binlog-do-db = jxbp_bpm
# binlog-do-db = jxbp_form
# binlog-do-db = jxbp_portal
# binlog-do-db = jxbp_system
# binlog-do-db = jxbp_seata
# binlog-do-db = jxbp_davinci
# binlog-do-db = jxbp_job
# binlog-do-db = jxbp_ureport
# binlog-do-db = jxbp_nacos
# binlog-do-db = ss_fyfkxt
重启mysql 服务
service mysql restart
1.4 查看配置
查看bin_log配置
mysql> show global variables like '%log%';
+--------------------------------------------+------------------------------------+
| Variable_name | Value |
+--------------------------------------------+------------------------------------+
| back_log | 80 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /var/lib/mysql/zmh.log |
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_write_ahead_size | 8192 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| log_bin | ON |
| log_bin_basename | /var/log/mysql/logbin |
| log_bin_index | /var/log/mysql/logbin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /var/log/mysql/error.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_basename | /var/lib/mysql/zmh-relay-bin |
| relay_log_index | /var/lib/mysql/zmh-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/zmh-slow.log |
| sql_log_off | OFF |
| sync_binlog | 1 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+--------------------------------------------+------------------------------------+
73 rows in set (0.00 sec)
1.5 创建用于从服务器同步数据使用的帐号
mysql –uroot –p
zsyqfk*CEC
create user 'copyDB'@'%' identified by 'copyDB@123';
GRANT REPLICATION SLAVE ON *.* TO 'copyDB'@'%';
FLUSH PRIVILEGES;
1.6 获取主服务器的二进制日志信息
主机执行：
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| logbin.000003 | 154 | | mysql | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
File为使用的日志文件名字，Position为使用的文件位置，这两个参数须记下，配置从服务器时会用到
1.7 配置从服务器(centos 7)
[mysqld]
# server-id 这里为我的ipv4地址的最后一段
server-id=100
# 开启mysql binlog功能
log-bin = mysql-bin
# 允许跳过错误
# slave-skip-errors=all
# binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW
# 减少记录日志的内容，只记录受影响的列
binlog_row_image = minimal
# 指定需要复制的数据库名 ——同步全部数据库，就不需要写下面的库名称
#binlog-do-db = jxbp_user
#binlog-do-db = jxbp_bpm
#binlog-do-db = jxbp_form
#binlog-do-db = jxbp_portal
重启服务
systemctl restart mysqld.service
设置主服务器
# master_log_file master_log_pos 写上面主库的版本
change master to master_host='172.21.63.40',master_user='copyDB',master_password='copyDB@123',master_log_file='mysql-bin.000004',master_log_pos=401;
CHANGE MASTER 详细用法（根据实际情况设置偏移量）
https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
开启服务
start slave ;
#查看状态
show slave status;
stop slave;
mysql 删除数据导致主从库同步停止问题
第一步，停从库的同步服务 stop slave;
第二步：跳过当前错误
set global sql_slave_skip_counter=10;（如果删除数据量则加大数值）
第三步：启动从库服务 start slave;
参考文章：
6203
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
