mysql 从服务器_mysql主从服务器配置

1.mysql主从服务器介绍：

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

简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步。

MySQL主从是基于binlog的，主上须开启binlog才能进行主从。

2.主从过程:

主将更改操作记录到binlog中

从将主的binlog事件(SQL语句)同步到本机并记录在relaylog中

从根据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.00sec)

#记住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.测试主从