mysql主从部署文档

master,slave安装mysql

参考：link.

一、在master操作

1、 在master上创建测试库

mysql> CREATE DATABASE yzhao66 CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use yzhao66 ;
Database changed

mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.02 sec)
     
mysql> insert into yzhao66 .haha values(1,"yangzhuohao"),(2,"yangzhuohao");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from huanqiu.haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | yangzhuohao |
|  2 | yangzhuohao |
+----+-------------+
2 rows in set (0.00 sec)

2、导出多余的数据到slave库

[[e-mail] ~]# mysqldump -uroot yzhao66 -proot123 >/opt/yzhao66.sql
[[e-mail] ~]# rsync  -e "ssh -p22" -avpgolr /opt/yzhao66.sql 10.0.20.165:/opt/

3、设置数据同步权限

mysql> grant replication slave,replication client on *.* to [e-mail]'10.0.20.165' identified by "[e-mail]";
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4、修改my.conf文件

[[e-mail] ~]# vim /etc/my.conf

（1)、 在最下面加入

server-id=1        #数据库唯一ID，主从的标识号绝对不能重复。
log-bin=mysql-bin    #开启bin-log，并指定文件目录和文件名前缀
binlog-do-db=yzhao66  #需要同步的数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）。
binlog-ignore-db=mysql  #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
sync_binlog = 1      #确保binlog日志写入后与硬盘同步
binlog_checksum = none  #跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed   #bin-log日志文件格式，设置为MIXED可以防止主键重复。

（2）、重启mysql

[[e-mail] ~]# systemctl restart mysqld

5、 查看mysql状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      150 | yzhao66      | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

二、在slave上操作

1、修改my.conf文件

[[e-mail]~]# vim /etc/my.conf

（1）、在最下面加入

server-id=2   #设置从服务器id，必须于主服务器不同
log-bin=mysql-bin   #启动MySQ二进制日志系统
replicate-do-db=yzhao66  #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。
replicate-ignore-db=mysql  #不同步mysql系统数据库
slave-skip-errors = all   #跳过所有的错误错误，继续执行复制操作

（2）、重启mysql

[[e-mail] ~]# systemctl restart mysqld

2、在slave数据库中导入从master传过来的数据。

mysql> CREATE DATABASE yzhao66 CHARACTER SET utf8 COLLATE utf8_general_ci;  #先创建一个yzhao66空库，否则下面导入数据时会报错说此库不存在。
mysql> use yzhao66;
mysql> source /opt/yzhao66.sql;   ＃导入master中多余的数据。
mysql> select*from haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | yangzhuohao |
|  2 | yangzhuohao |
+----+-------------+
2 rows in set (0.00 sec)

3、 配置主从同步指令

mysql> stop slave;
mysql> change  master to master_host='10.0.20.164',master_user='slave',master_password='[e-mail]',master_log_file='mysql-bin.000002',master_log_pos=150;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20201013105855449.png#pic_center)

mysql> start slave;
mysql> show slave status \G;

如果Slave_IO_Running和Slave_SQL_Running都是yes话就成功了

三、测试

master(写数据):

slave(读数据）:

四、出现的问题解决

1、“error connecting to master ‘[e-mail]:3306’ - retry-time: 60 retries: 2”

解决：
关闭两边防火墙

firewall-cmd --state
systemctl stop firewalld.service
systemctl disable firewalld.service 

2、“Got fatal error 1236 from master when reading data from binary log: ‘binlog truncated in the middle of event; consider out of disk space on master; the first event ‘mysql-bin.000003’ at 120, the last event read from ‘./mysql-bin.000003’ at 123, the last byte read from ‘./mysql-bin.000003’ at 139.’”
解决: 链接link.