mysql一主多从配置

配置mysql一主多从

1.尽量保持mysql版本一致，我这里用的是v5.6
2.关闭防火墙或开放端口
3.部署环境

分类	ip
master	192.168.0.15
slave	192.168.0.147
slave	192.168.0.174

配置数据库文件

1.Master

#修改my.cnf
vi /etc/my.cnf

log-bin=mysql-bin #开启二进制日志功能（必须有）
binlog_format=mixed
server-id = 1  #不能和从服务器冲突
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
relay-log = relay-bin
relay-log-index = slave-relay-bin.index

2.Slave1

#修改my.cnf
vi /etc/my.cnf

log-bin=mysql-bin #开启二进制日志功能（必须有）
binlog_format=mixed
server-id = 2  #不能和其他服务器冲突
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
relay-log = relay-bin
relay-log-index = slave-relay-bin.index

2.Slave2

#修改my.cnf
vi /etc/my.cnf

log-bin=mysql-bin #开启二进制日志功能（必须有）
binlog_format=mixed
server-id = 3  #不能和其他服务器冲突
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
relay-log = relay-bin
relay-log-index = slave-relay-bin.index

修改完配置文件后重启服务：systemctl restart mysqld

配置数据主从

1.Master

授权从服务器用户

mysql -u你的用户 -p你的密码

#创建slave1用户，密码123456，权限给192.168.0.147
mysql> grant replication slave on *.* to 'slave1'@'192.168.0.147' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

#创建slave2用户，密码123456，权限给192.168.0.174
mysql> grant replication slave on *.* to 'slave2'@'192.168.0.174' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

#查看master状态， mysql-bin.000009和332用于slave配置
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000009
         Position: 332
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

2.Slave1

使用change master语句，确定master库的ip、用户、密码等信息

#master_log_file值在master查询状态时获取的File
#master_log_pos值在master查询状态时获取的Position
mysql> change master to 
    -> master_host='192.168.0.15',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000009',
    -> master_log_pos=332;   
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#验证Slave_IO_Running、Slave_SQL_Running是否是YES，两个都是YES表示mysql主从复制配置成功了。
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.15
                  Master_User: slave1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 332
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes      #为Yes成功
            Slave_SQL_Running: Yes   #为Yes成功

3.Slave2

使用change master语句，确定master库的ip、用户、密码等信息

mysql> change master to
    -> master_host='192.168.0.15',
    -> master_user='slave2',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000009',
    -> master_log_pos=332;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#验证Slave_IO_Running、Slave_SQL_Running是否是YES，两个都是YES表示mysql主从复制配置成功了。
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.15
                  Master_User: slave2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 332
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

Slave_IO_Running: Yes(负责从库去主库读取二进制日志，并写入到从库的中继日志)

Slave_SQL_Running: Yes(负责将中继日志准换成SQL语句后执行)

4.开启同步

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

5.验证同步是否成功

测试主从是否同步（master数据库上执行Sql、Slave上面去验证）

Master:

mysql> create database lxqdb;

mysql> create table lxqdb.lxq(id int,name varchar(50),sex varchar(10));

mysql> insert into lxqdb.lxq(id,name,sex) values (1,'lxq','male');

Slave:

mysql> show databases;
+--------------------+
| Database     |
+--------------------+
| information_schema|
| lxqdb       |
| mysql       |
| performance_schema|
+--------------------+
4 rows in set (0.01 sec)

mysql> use lxqdb;
mysql> select * from lxqdb.lxq;
+------+------+------+
| id | name| sex |
+------+------+------+
| 1  | lxq | male|
+------+------+------+
1 row in set (0.00 sec)

从库只读的配置

对于需要保证master-slave主从同步的salve库，如果要设置为只读状态，需要执行的命令为：

mysql> set global read_only=1;

read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户（如超级管理员root用户）的数据修改操作。

在MySQL中设置read_only=1后，普通的应用用户进行insert、update、delete等会产生数据变化的DML操作时，都会报出数据库处于只读模式不能发生数据变化的错误，

但具有super权限的用户，例如在本地或远程通过root用户登录到数据库，还是可以进行数据变化的DML操作；

为了确保所有用户，包括具有super权限的用户也不能进行读写操作，就需要执行给所有的表加读锁的命令

mysql> flush tables with read lock;

这样使用具有super权限的用户登录数据库，想要发生数据变化的操作时，也会提示表被锁定不能修改的报错。

将slave数据库read-only=1设置只读后，在master执行GRANT USAGE ON . TO ‘user01’@‘localhost’ IDENTIFIED BY’123456’ WITH GRANT OPTION;创建一个普通用

户，然后用普通用户登录从库，执行操作会报错。切换到root用户后还是可以进行增删改查的。

常见问题

Slave_IO_Running: NO

一般的原因是：

1> master服务器的网络不通，或者master服务器的防火墙拒绝了外部连接3306端口,selinux模式选择都可能会影响结果。

2> 在配置slave服务器时，使用chage master语句时输错了ip地址和密码等信息。

3> 在master主服务器授权了错误的IP地址，权限。

解决方法：

关闭防火墙或开通3306的访问权限，selinux设置为disabled;

slave的服务器上使用master授权用户登陆一下master数据库确保授权和slave地址能够访问，最后slave库检查一下change master语句是否有误;

Slave_SQL_Running: No

mysql同步故障原因：

1> 程序可能在slave上进行了写操作

2> 也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成

解决方法1：

mysql> stop slave;

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> start start;

备注：不一定会生效，操作两次不生效后，选择第二种方法

解决方法2：

mysql> stop slave;  

mysql> show master status;

master服务器上查看主机状态,记录File和Position对应的值，然后到slave服务器上执行手动同步(change master语句)，参考同步时使用的语句。

备注：强制从某一个点同步，这种方法会停掉master写操作，会丢失部分没有同步的数据，不影响使用。

mysql主从复制存在的问题

1> 主库宕机后，数据可能丢失

2> 从库只有一个sql Thread，主库写压力大，复制很可能延时

解决方法：

半同步复制---解决数据丢失的问题

异行复制----解决从库复制延迟的问题