CentOS6.5搭建MySQL主从复制，读写分离(冷月宫主亲自整理，最简单明了)

CentOS6.5搭建MySQL主从复制，读写分离


MySQL主从复制的优点:
1、 如果主服务器出现问题， 可以快速切换到从服务器提供的服务，保证高可用性
2、 可以在从服务器上执行查询操作， 降低主服务器的访问压力
3、 可以在从服务器上执行备份， 以避免备份期间影响主服务器的服务


注意事项：
1、server-id必须唯一，一般使用ip的后三位
2、从库Slave_IO_Running:NO 可能原因:帐号无权限操作
3、Can't execute the query because you have a conflicting read lock，解锁下即可 unlock tables;
4、一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从服务器查询， 实时性要求高的数据仍然需要从主数据库获得
5、修改完主从服务器的配置需要重启mysql：service mysqld restart


MySQL_Proxy：192.168.10.211 4040
MySQL_Master: 192.168.10.212 3306
MySQL_Slave01: 192.168.10.213 3306
MySQL_Slave02: 192.168.10.214 3306


请先分别安装mysql，版本需一致，装了即可跳过


一、安装mysql(master和slave)


yum -y install mysql mysql-server #输入y即可自动安装,直到安装完成


设置防火墙开放3306端口。
vim /etc/sysconfig/iptables
service iptables restart


service mysqld start
mysqladmin -u root password 'MySQL@163.com'
mysql -u root -p


set password for root@localhost=password('MySQL@163.com');
create user 'MySQL'@'%' identified by 'MySQL@163.com'; 
grant all privileges on *.* to 'MySQL'@'localhost' identified by 'MySQL@163.com';
grant all privileges on *.* to 'MySQL'@'%' identified by 'MySQL@163.com';
GRANT ALL PRIVILEGES ON *.* TO 'MySQL'@'%' IDENTIFIED BY 'MySQL@163.com' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
flush privileges;




1、先登录主机 A，在主服务器上，设置一个从数据库的账户，使用REPLICATION SLAVE（从复制）赋予权限，如：
mysql>create user 'slave01'@'192.168.10.213' identified by 'MySQL@163.com'; 
mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave01'@'192.168.10.213' IDENTIFIED BY 'MySQL@163.com';


mysql>create user 'slave02'@'192.168.10.214' identified by 'MySQL@163.com'; 
mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave02'@'192.168.10.214' IDENTIFIED BY 'MySQL@163.com';


赋予从机权限，有多台从机，就执行多次。
mysql>flush privileges;
mysql>exit;
2、 打开主机A的my.cnf，输入如下：（修改主数据库的配置文件my.cnf，开启BINLOG，并设置server-id的值，修改之后必须重启mysql服务）
cp /etc/my.cnf /etc/my.cnf.bak
vim /etc/my.cnf 
server-id=1    #主机标示，整数
log_bin=mysql-bin #确保此文件可写，开启bin-log
read-only=0  #主机，读写都可以
binlog-do-db=test   #需要备份数据，多个写多行
binlog-ignore-db=mysql #不需要备份的数据库，多个写多行


重启mysql服务:
service mysqld restart
mysql -u root -p


可以通过mysql>show variables like 'log_%'; 验证二进制日志是否已经启动。
3、现在可以停止主数据的的更新操作，并生成主数据库的备份，我们可以通过mysqldump到处数据到从数据库，当然了，你也可以直接用cp命令将数据文件复制到从数据库去，注意在导出数据之前先对主数据库进行READ LOCK，以保证数据的一致性
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.19 sec)


然后mysqldump导出数据：
mysqldump -h127.0.0.1 -p3306 -uroot -p test > /opt/test.sql


4、得到主服务器当前二进制日志名和偏移量，这个操作的目的是为了在从数据库启动后，从这个点开始进行数据的恢复。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      517 | test         | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)




最好在主数据库备份完毕，恢复写操作。
mysql> unlock tables;
Query OK, 0 rows affected (0.28 sec)


5、将刚才主数据备份的test.sql复制到从数据库（navicat、phpmyadmin、命令行都可以），进行导入。
scp -r -P 22 /opt/test.sql root@192.168.10.213:/opt/
scp -r -P 22 /opt/test.sql root@192.168.10.214:/opt/


mysql -u root -p test < /opt/test.sql
6、修改从数据库的my.cnf，增加server-id参数，指定复制使用的用户，主数据库服务器的ip，端口以及开始执行复制日志的文件和位置。打开从机B的my.cnf，输入（修改之后必须重启mysql服务）
cp /etc/my.cnf /etc/my.cnf.bak
vim /etc/my.cnf 


server-id=2
log_bin=mysql-bin
master-host=192.168.10.212
master-user=slave01
master-pass=MySQL@163.com
master-port=3306
master-connect-retry=60 #如果从服务器发现主服务器断掉，重新连接的时间差(秒)
replicate-do-db=test #只复制某个库
replicate-ignore-db=mysql #不复制某个库
slave-skip-errors


7、在从服务器上,启动slave进程
mysql -u root -p
mysql> start slave;


8、在从服务器进行show salve status验证


mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.212
                  Master_User: slave01
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000006
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 552
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)


提示
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明配置成功了




如果有NO，则做如下操作：
在Master机操作：
service mysqld restart
show master status;


mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      954 | test         | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


mysql> 


在Slave机操作：
SLAVE STOP; 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=954; 
SLAVE START; 
show slave status\G


如果还是有NO就要注意一下在Master机上的授权语句,需要对slave授REPLICATION SLAVE权限。


9、测试主从服务器是否能同步 
插入 修改 删除 增加字段 修改字段 增加表自己测试都可以


[linuxidc@server22 ~]$mysql -uroot -pMySQL@163.com
mysql> create database test;  或   use test;
mysql> create table user(id int);
mysql> insert into user values(1),(2),(3),(4),(5),(6);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from user;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2rows in set (0.00 sec)




mysql> select * from user;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)




mysql> update user set id=11 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0




mysql> select * from user;
+----+
| id |
+----+
| 11 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)




mysql> delete from user where id=2;
Query OK, 1 row affected (0.00 sec)




mysql> select * from user;
+----+
| id |
+----+
| 11 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
5 rows in set (0.00 sec)




mysql> alter table user add name varchar(50);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0




mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 11 | NULL |
|  3 | NULL |
|  4 | NULL |
|  5 | NULL |
|  6 | NULL |
+----+------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE user  MODIFY COLUMN name VARCHAR(200);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0




mysql> desc user;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> create table user2(id int);
Query OK, 0 rows affected (0.01 sec)


mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
| user           |
| user2          |
+----------------+
3 rows in set (0.00 sec)
mysql>






在从服务器查看是否同步过来 如果一致说明成功


mysql> use test;
Database changed
mysql> select * from user;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
2 rows in set (0.00 sec)


mysql> select * from user;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)


mysql> select * from user;
+----+
| id |
+----+
| 11 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)


mysql> select * from user;
+----+
| id |
+----+
| 11 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
5 rows in set (0.00 sec)


mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 11 | NULL |
|  3 | NULL |
|  4 | NULL |
|  5 | NULL |
|  6 | NULL |
+----+------+
5 rows in set (0.00 sec)
mysql> desc user;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
| user           |
| user2          |
+----------------+
3 rows in set (0.00 sec)


mysql>


mysql binlog日志查看： 


show binlog events\G;


*************************** 12. row ***************************
   Log_name: mysql-bin.000007
        Pos: 985
 Event_type: Query
  Server_id: 1
End_log_pos: 1075
       Info: use `test`; delete from user where id=2
*************************** 13. row ***************************
   Log_name: mysql-bin.000007
        Pos: 1075
 Event_type: Query
  Server_id: 1
End_log_pos: 1175
       Info: use `test`; alter table user add name varchar(50)
*************************** 14. row ***************************
   Log_name: mysql-bin.000007
        Pos: 1175
 Event_type: Query
  Server_id: 1
End_log_pos: 1287
       Info: use `test`; ALTER TABLE user  MODIFY COLUMN name VARCHAR(200)
*************************** 15. row ***************************
   Log_name: mysql-bin.000007
        Pos: 1287
 Event_type: Query
  Server_id: 1
End_log_pos: 1376
       Info: use `test`; create table user2(id int)
15 rows in set (0.00 sec)


本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-09/135121.htm




二.安装mysql-proxy
实现读写分离是由lua脚本实现的，现在mysql-proxy里面已经集成，无需再安装
cd /u01/
wget http://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy
3.配置mysql-proxy
cd mysql-proxy
mkdir lua #创建脚本存放目录
mkdir logs #创建日志目录
mkdir conf #放置配置文件
cp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件
cp share/doc/mysql-proxy/admin-sql.lua ./lua #复制管理脚本


vim conf/mysql-proxy.cnf      (选项值后不能有任何空格，keepalive也是)
[mysql-proxy]
daemon=true
#以守护进程方式运行
user=root
#运行mysql-proxy用户
pid-file=/u01/mysql-proxy/logs/mysql-proxy.pid
#进程pid文件
keepalive=true
#mysql-proxy崩溃时，尝试重启
event-threads=4
#event-handing线程数，默认值是1
max-open-files=2048
#最大文件句柄数


log-level=info
#日志级别：error|warning|info|message|debug
#log-use-syslog=true  
#日志使用syslog，和log-file只能开启一个
log-file=/u01/mysql-proxy/logs/mysql-proxy.log
#日志文件


admin-username=MySQL
#主从mysql共有的用户
admin-password=MySQL@163.com
#用户的密码
admin-address=192.168.10.211:4040
#mysql-proxy运行ip和端口，不加端口，默认4040
admin-lua-script=/u01/mysql-proxy/lua/admin-sql.lua
#指定管理脚本


proxy-skip-profiling=true
#是否禁用查询性能剖析
proxy-backend-addresses=192.168.10.212:3306
#后端主mysql的ip和port
proxy-read-only-backend-addresses=192.168.10.213:3306,192.168.10.214:3306
#指定后端从slave读取数据，多个以逗号分隔，超过2条换行写
proxy-lua-script=/u01/mysql-proxy/lua/rw-splitting.lua
#指定读写分离配置文件位置




由于安全要求，必须将配置文件权限设为660（创建人可读写，同组人可读），否则不允许启动。
chmod 660 conf/mysql-proxy.cnf
4.修改读写分离脚本
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1, --默认超过4个连接数时，才开始读写分离，改为1
                max_idle_connections = 1, --默认8，改为1


                is_debug = false
        }
end
5.启动mysql-proxy
./bin/mysql-proxy --defaults-file=conf/mysql-proxy.cnf


使用多个客户端查询，添加数据测试。