mysql主从服务器配置

1.mysql主从服务器介绍：

MySQL主从又叫做Replication、AB复制。

简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步。
MySQL主从是基于binlog的，主上须开启binlog才能进行主从。

2.主从过程:

1. 主将更改操作记录到binlog中
2. 从将主的binlog事件（SQL语句）同步到本机并记录在relaylog中
3. 从根据relaylog里面的SQL语句按顺序执行

说明： 

• 该过程有三个线程 ：主上有一个log dump线程，用来和从的i/o线程传递binlog；
• 从上有两个线程，其中i/o线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的SQL语句落地。

 

 

3.准备工作：
阿里云的centos：

两台服务器：（mysql版本尽量保持一致，主服务器的版本不能高于从服务器） 

主服务器：ip1;
从服务器：ip2；
待同步的数据库：slaveDB;
同步主从数据库数据，保持主从数据一致！

需要注意的是！！！
线上的项目导出数据的时候，保证主库上锁，等slave导入、slave start之后在解锁；
slave重启的时候也一样，重启之前，主库上锁，重启完毕，解锁；

主库表锁！

flush tables with read lock

主库解锁！

unlock tables

 

主从配置参数介绍：

主服务器:
binlog-do-db= 仅同步指定的库
binlog-ignore-db= 忽略指定的库

从服务器：
replicate_do_db= 同步指定的库
replicate_ignore_db= 忽略指定的库
replicate_do_table= 同步指定的表
replicate_ignore_table= 忽略指定的表

replicate_wild_do_table=    如aming.%，支持通配符
replicate_wild_ignore_table=

 

说明： 进行从服务器的配置时尽量使用参数“replicate_wild_”，使匹配更精确，提升使用性能。

 

4.配置主服务器：

4.1.修改mysql配置文件

vim /etc/my.conf

server-id = 1  #这是数据库ID,此ID是唯一的，主库默认为1，其他从库以此ID进行递增，ID值不能重复，否则会同步出错；
log-bin = mysql-bin  #二进制日志文件，此项为必填项，否则不能同步数据；
binlog-do-db = slaveDB  #需要同步的数据库，如果需要同步多个数据库；则继续添加此项。
# binlog-do-db = slaveDB1
# binlog-do-db = slaveDB2
binlog-ignore-db = mysql 不需要同步的数据库；

 

4.2保存退出！重启mysql服务，使更改生效！

service mysql restart
或者：
/etc/init.d/mysqld restart

 

4.3查看同步和不同步的数据库有哪些

mysql> show variables like 'server_id';

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
|   mysql-bin.000013|    10844 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#记住file和position(设置主从同步时会使用)

4.4添加一个同步用户slaveUser并赋值权限（用于从服务器slave使用）！

[root@123 mysql]# mysql -uroot -p123456
mysql> create user slaveUser;
mysql> grant replication slave on *.* to 'slaveUser'@'192.168.1.130' identified by '123456';

 

4.5主服务器锁表：

mysql> flush tables with read lock;   #锁定数据表（目的是暂时使其不能继续写，保持现有状态用于同步）

备份主库中需要同步的数据库：

[root@123 mysql]# mysqldump -uroot -p123456 db1 > /backup/db1.sql

[root@123 mysql]# mysqldump -uroot -p123456 db2 > /backup/db2.sql

 

5.配置从服务器

准备：导入主服务器上的数据库

# scp ./db1.sql root@192.168.1.16:~

# mysql -uroot -hlocalhost -p slaveDB > ./db1.sql;

 

 

5.1.编辑配置文件：

vim /etc/my.conf

server-id=2    #默认是1改成2
log-bin=mysql-bin    #这行本身有
replicate-do-db=slaveDB    #需要同步的数据库
replicate-ignore-db=mysql    #不同步系统数据库
read_only     #设只读权限

 

5.2保存退出！重启mysql服务，使更改生效！

service mysql restart
或者：
/etc/init.d/mysqld restart

5.3查看结果：

mysql> show variables like 'server_id';

5.4.0设置主服务器上用户的权限：

grant file on *.* to slaveUser@'%' identified by '123456';

flush privileges;

5.4修改slave参数，设置主从同步：

[root@localhost ~]# mysql -uroot
mysql> stop slave;
mysql> change master to master_host='192.168.8.132',master_user='slaveUser',master_password='123456',master_log_file='mysql-bin.00001',master_log_pos=10844;
# 注：master_log_file=上面提到的二进制文件；master_log_pos=上面提到的pos ,master_host为主的IP；file、pos分别为主的filename和position。

 
# 启用主从同步：
mysql> start slave;
Query OK, 0 rows affected (0.22 sec)

检测主从是否建立成功

mysql> show slave status\G;

1）Slave_IO_Running：yes
该参数可作为io_thread的监控项，Yes表示io_thread的和主库连接正常并能实施复制工作，No则说明与主库通讯异常，多数情况是由主从间网络引起的问题；
   2）Slave_SQL_Running：yes
该参数代表sql_thread是否正常，YES表示正常，NO表示执行失败，具体就是语句是否执行通过，常会遇到主键重复或是某个表不存在。
   3）Seconds_Behind_Master：0
是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp(简写为ts)进行比较，而得到的这么一个差值；
        NULL—表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No，而非Yes。
        0 — 该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在。
        正值 — 表示主从已经出现延时，数字越大表示从库落后主库越多。
        负值 — 几乎很少见，我只是听一些资深的DBA说见过，其实，这是一个BUG值，该参数是不支持负值的，也就是不应该出现。

 

 5.5解锁主库的表（在主上操作）：

[root@123 mysql]# mysql -uroot -p123456
mysql> unlock tables;

至此主从配置搭建完成！！！

说明：以上搭建的是主服务器用于写操作，从服务器用于读操作。

问题1：若是slave status 里面有报错，手动解决之后，执行如下命令即可！

mysql> slave stop;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER =1;
mysql> slave start;

问题2：在slave上执行show slave status\G，结果中显示Last_IO_Error: error connecting to master ……

先在主服务器上确认复制用户账户是否存在且是否赋了正确的权限：

 mysql> show grants for 'slaveUser'@'%';

显示没问题：GRANT REPLICATION SLAVE ON *.* TO 'slaveUser'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

然后，在从服务器上使用该账户连接至主：

[root@localhost data]# mysql -u salveUser -h 192.168.1.10 -p -P3306

 

提示密码错误~~~

最后，在从上修改连接密码：

mysql>stop slave;
mysql>reset slave;
mysql>change master to master_host='192.168.1.10',
 master_user='slaveUser',master_password='123456',
master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=120;
mysql>start slave;
mysql>show slave status\G;

 

 防火墙原因：

查看3306端口是否开放：

systemctl status firewalld #查看firewall启动情况
firewall-cmd --query-port=3306/tcp 检查3306端口是否已经开启，如果显示yes,则表示防火墙已开启该端口。
firewall-cmd --zone=public --add-port=3306/tcp --permanent 开启3306端口
firewall-cmd --reload # 　然后重启 firewalld 

 

firewall-cmd --query-port=3306/tcp

 

复制帐号权限原因：

 select * from user where user='slaveUser'\G;
update user set Grant_pri='Y' where user='slaveUser';
flush privileges;

 

6.主主双向服务器

如果要搭建主从服务器，主从都可以写数据库、读数据库。就是主主双向服务器，在以上配置的基础上，增加以下步骤即可：

• 在从服务器，重复上面4.4的 操作； 
• 在主服务器，重复上面5.4的操作即可。

7.测试主从