MySQL单机安装和配置，这里就不展开了。感兴趣的同学可以参考我之前的文章《MySQL-8.0.11源码编译安装》
实际生产环境往往是单库运行一段时间后，增加的从库，需要考虑历史数据同步问题。这里是在测试环境搭建的，不考虑历史数据问题。直接在两台数据库服务器上创建两个新库niliu, 然后演示同步。
主：10.235.25.242 【CentOS release 6.5】 【MySQl 5.0.77】
从：10.235.25.241 【CentOS release 6.9】 【MySQl 5.0.77】
说明：以下配置为了简洁方便，含义特放在第五部分说明。
一、主机配置
0、修改MySQL配置
打开/etc/my.cnf文件，在[mysqld]下增加如下内容
server_id = 1 log-bin = mysql-bin binlog-do-db = niliu log-slave-updates sync_binlog = 1 auto_increment_offset = 1 auto_increment_increment = 1 expire_logs_days = 7 log_bin_trust_function_creators = 1 skip-name-resolve
特别注意把binlog-do-db的值改为你直接的库名。
1、重启MySQL
[root@242 chuanbo7]# /etc/init.d/mysql restart Shutting down MySQL. .... [确定] Starting MySQL. [确定]
2、登录主机MySQL
/usr/bin/mysql -h10.235.25.242 -uroot -p123456 -P3306 --default-character-set=utf8
3、创建同步数据账号
账号名mysql(容易有奇异)
mysql>grant replication slave on *.* to 'mysql'@'10.235.25.241' identified by '123456'; #更新数据库权限 mysql>flush privileges;
创建账号的原理可以参考后文说明3。
4、查看主服务器状态
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000147 | 98 | db | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
记录下File和Position的内容，从库配置会用到。
二、从机配置
0、修改MySQL配置
打开/etc/my.cnf文件，在[mysqld]下增加如下内容
server_id = 2 log-bin = mysql-bin log-slave-updates sync_binlog = 0 innodb_flush_log_at_trx_commit = 0 replicate-do-db = niliu slave-net-timeout = 60 log_bin_trust_function_creators = 1
特别注意replicate-do-db值为同步的数据库名。
1、登录从服务器
/usr/bin/mysql -h10.235.25.241 -uroot -p123456 -P3306 --default-character-set=utf8
2、执行同步命令
# 执行同步命令，设置主服务器ip，同步账号密码，同步位置 mysql>change master to master_host='10.235.25.242',master_user='mysql',master_password='123456',master_log_file='mysql-bin.000147',master_log_pos=98; 注意查看master_log_file内容 # 开启同步功能 mysql>start slave;
3、查看从服务器状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.235.25.242
Master_User: mysql
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000147
Read_Master_Log_Pos: 98
Relay_Log_File: dev241-relay-bin.000001
Relay_Log_Pos: 98
Relay_Master_Log_File: mysql-bin.000147
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: niliu
注意\G后面不用加; 不然多余提示ERROR: No query specified
注意观察以下几点
a、观察I/O线程和SQL线程是否正常运行：即Slave_IO_Running和Slave_SQL_Running都是Yes态
b、观察Master_Log_File和Read_Master_Log_Pos值是否跟主库一致。
c、观察Replicate_Do_DB是否是要同步的库名。
如果a、b、c都满足了，可以继续往下进行。现实往往不会让你这么顺利，遇到问题很正常，可以参考第三步的问题。
三、主从同步验证
0、在主库和从库中分别创建新库表
create database niliu; use niliu; create table info ( `id` int(10) unsigned not null AUTO_INCREMENT comment '主键ID', `type` varchar(2) not null default '' comment '类型', `content` text comment '内容', `ctime` timestamp not null default CURRENT_TIMESTAMP comment '创建时间', `mtime` timestamp not null default '0000-00-00 00:00:00' comment '修改时间', PRIMARY KEY (`id`), key `idx_type` (`type`) using BTREE )ENGINE = InnoDB default CHARSET = 'utf8' comment '信息表';
1、在主库中执行insert语句
insert into info (type, content) values (1, 'PHP'); insert into info (type, content) values (1, 'Golang'); insert into info (type, content) values (1, 'Lua'); insert into info (type, content) values (1, 'Python');
2、在主库中查看
mysql> select * from info; +----+------+---------+---------------------+---------------------+ | id | type | content | ctime | mtime | +----+------+---------+---------------------+---------------------+ | 1 | 1 | PHP | 2019-11-28 20:26:24 | 0000-00-00 00:00:00 | | 2 | 1 | Golang | 2019-11-28 20:26:25 | 0000-00-00 00:00:00 | | 3 | 1 | Lua | 2019-11-28 20:26:25 | 0000-00-00 00:00:00 | | 4 | 1 | Python | 2019-11-28 20:26:27 | 0000-00-00 00:00:00 | +----+------+---------+---------------------+---------------------+ 4 rows in set (0.00 sec)
3、在从库中查看
mysql> select * from info; +----+------+---------+---------------------+---------------------+ | id | type | content | ctime | mtime | +----+------+---------+---------------------+---------------------+ | 1 | 1 | PHP | 2019-11-28 20:26:24 | 0000-00-00 00:00:00 | | 2 | 1 | Golang | 2019-11-28 20:26:25 | 0000-00-00 00:00:00 | | 3 | 1 | Lua | 2019-11-28 20:26:25 | 0000-00-00 00:00:00 | | 4 | 1 | Python | 2019-11-28 20:26:27 | 0000-00-00 00:00:00 | +----+------+---------+---------------------+---------------------+ 4 rows in set (0.00 sec)
如果内容和主库一致说明配置运行正常，否则可参考第四部分排除错误。
4、update和delete同理，可自行验证。
四、问题
0、发现Slave_IO_Running不是Yes, 查看从机MySQL error log
[root@241 chuanbo7]# tail -f /data1/logs/mysqld.log 191126 20:03:32 mysqld started 191126 20:03:32 InnoDB: Started; log sequence number 0 915174986 191126 20:03:32 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.0.77-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL) 191126 20:13:44 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=dev241-relay-bin' to avoid this problem. 191126 20:13:55 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000147' at position 98, relay log './dev241-relay-bin.000001' position: 4 191126 20:13:57 [Note] Slave I/O thread: connected to master 'mysql@10.235.25.242:3306', replication started in log 'mysql-bin.000147' at position 98 191126 20:13:57 [ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). 191126 20:13:57 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000147', position 98
很容易发现这句[ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids;说明server_id配置的不一样。先检查my.cnf配置是否区分了。我这里检查/etc/my.cnf中确实区分了配置的server_id = 2。但是通过show variables查看却不是/etc/my.cnf中定义的server_id
mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ 1 row in set (0.00 sec)
后来发现从库MySQL安装目录下的my.cnf也指定了server-id = 1，优先读取了安装目录下的配置内容。修改安装目录下的server-id = 2即可。关于MySQL配置加载顺序，可以参考后文第五部分说明。
1、无法同步，error log中有如下错误Error: ‘Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111′ errno: 2013
观察从机error log
191128 12:54:55 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000147' at position 98, relay log './dev241-relay-bin.002406' position: 235 191128 12:54:55 [Note] Slave I/O thread: connected to master 'mysql@10.235.25.242:3306', replication started in log 'mysql-bin.000147' at position 98 191128 12:55:00 [Note] Slave: received end packet from server, apparent master shutdown: 191128 12:55:00 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000147' position 98 191128 12:55:00 [ERROR] Slave I/O thread: error reconnecting to master 'mysql@10.235.25.242:3306': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 111' errno: 2013 retry-time: 60 retries: 86400
MySQL 2013错误开始误以为是权限问题，登录后验证正常排除。
在从机上用新账号登录主机数据库。各自账号登录只能看到各自的授权。
/usr/bin/mysql -h10.235.25.242 -umysql -p123456 -P3306 --default-character-set=utf8 mysql> show grants; +------------------------------------------------------------------------------------------------------------------------------+ | Grants for mysql@10.235.25.241 | +------------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'mysql'@'10.235.25.241' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
后来查看知道是DNS反向解析问题, 在主库的/etc/my.cnf的[mysqld]下增加如下命令即可。具体含义可见这里。
skip-name-resolve
3、时间不同步
发现主比从快5分钟，同步操作可见文章《Linux服务器时钟同步》
4、结果master、slave状态都正常，也没有error log还是不能同步
排查了2小时，后来无意中发现主库中binlog-do-db是db, 从库replicate-do-db是niliu。
细节是魔鬼啊，这种问题很难发现。为啥没有提示呢？思考。。。
5、其他查阅的同步异常错误
主从复制错误处理总结
Mysql主从（主从不同步解决办法，常见问题及解决办法，在线对mysql做主从复制）
MySQL主从不一致情形与解决方法
五、说明
0、主库my.cnf参数说明：
# 主数据库端ID号 server_id = 1 # 开启二进制日志 log-bin = mysql-bin # 需要复制的数据库名，如果复制多个数据库，重复设置这个选项或者用逗号隔开 binlog-do-db = db1, db2 # 将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中 log-slave-updates # 控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失) sync_binlog = 1 # 这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突 auto_increment_offset = 1 # 这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突 auto_increment_increment = 1 # 二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除 expire_logs_days = 7 # 将函数复制到slave log_bin_trust_function_creators = 1
1、从库my.cnf参数说明：
server_id = 2 log-bin = mysql-bin log-slave-updates sync_binlog = 0 # log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作 innodb_flush_log_at_trx_commit = 0 # 指定slave要复制哪个库 replicate-do-db = db # MySQL主从复制的时候，当Master和Slave之间的网络中断，但是Master和Slave无法察觉的情况下（比如防火墙或者路由问题）。Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据 slave-net-timeout = 60 log_bin_trust_function_creators = 1
2、MySQL创建同步账号说明
可以先建账号(账号名称自己起, account, slave都可以)， 在授权
2.1 创建账号
# 创建账号account，密码123456, 不限制登录IP CREATE USER account IDENTIFIED BY '123456'; # 创建账号限制登录IP CREATE USER account@10.235.25.241 IDENTIFIED BY '123456';
2.2 授权
grant 权限 on 数据库对象 to 用户
grant replication slave on *.* to 'mysql'@'10.235.25.241'
2.3 但是上面一条语句就搞定了
mysql>grant replication slave on *.* to 'mysql'@'10.235.25.241' identified by '123456';
关于授权可参考：mysql的grant权限
3、MySQL配置文件说明
注意MySQL读取my.cnf文件的顺序：安装目录，然后是默认目录。
默认目录顺序查看通过命令
mysql --help|grep 'my.cnf'
注意：要用安装目录下的mysql
[root@241 chuanbo7]# whereis mysql
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/local/bin/mysql /usr/include/mysql /usr/local/mysql /usr/share/mysql /opt/lampp/bin/mysql.server /opt/lampp/bin/mysql /usr/share/man/man1/mysql.1.gz
[root@241 chuanbo7]# ps -ef | grep mysql
root 724 1 0 20:57 pts/1 00:00:00 /bin/sh ./bin/mysqld_safe --datadir=/data1/mysql --pid-file=/data1/mysql/dev241.pid
mysql 768 724 0 20:57 pts/1 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql --user=mysql --pid-file=/data1/mysql/dev241.pid --skip-external-locking --port=3306 --socket=/tmp/mysql.sock
root 1197 27039 0 21:15 pts/1 00:00:00 grep --color=auto mysql
[root@241 chuanbo7]# /usr/local/mysql/bin/mysql --help|grep 'my.cnf'
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
更详细的说明，可查看mysql 查看当前使用的配置文件my.cnf的方法
4、有人说需要在从库my.cnf中加以下指令，实测没必要。
skip_slave_start read_only
六、总结
实际出真知啊，看别人写的很简单，实际操作才真正明白。最后截一张图做个纪念。
参考：
MySQL主从配置详解
mysql主从同步复制错误解决一例
https://segmentfault.com/a/1190000008942618
https://learnku.com/laravel/t/3174/mysql-master-slave-replication-simple-configuration-simple-absolutely-can-be-used
https://juejin.im/post/5c9d8109f265da612f1bb019