MySQL主从复制与读写分离

gdsrawe

MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)实践

Mysql作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台Mysql作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。

因此，一般来说都是通过 主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力 这样的方案来进行部署与实施的。

如下图所示：

一、MySQL主从复制
场景描述：
主数据库服务器：192.168.1.120，MySQL已经安装，并且无应用数据。
从数据库服务器：192.168.1.121，MySQL已经安装，并且无应用数据。

1.1 主服务器上进行的操作

[iyunv@master support-files]# service mysql start
Starting MySQL..                                           [确定]
[iyunv@master support-files]#

通过命令行登录管理MySQL服务器
[iyunv@master support-files]# mysql -uroot -p123123

授权给从数据库服务器192.168.10.121

mysql> GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.1.121' identified by '123123';
Query OK, 0 rows affected (0.00 sec)
修改主数据库的配置文件my.cnf,开启BINLOG，并设置server-id的值，修改之后必须重启Mysql服务
log-bin = /opt/mysql/log/mysql-bin.log
server-id=1

查询主数据库状态

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

记录下 FILE 及 Position 的值，在后面进行从服务器操作的时候需要用到

1.2 配置从服务器
修改从服务器的配置文件/opt/mysql/etc/my.cnf
将 server-id = 1修改为 server-id = 10，并确保这个ID没有被别的MySQL服务所使用。

启动mysql服务

[iyunv@slave1 data]# service mysql restart
Shutting down MySQL..                                      [确定]
Starting MySQL...                                          [确定]

通过命令行登录管理MySQL服务器
[iyunv@slave1 data]# mysql -uroot -p123123

执行同步SQL语句

mysql> change master to
    -> master_host='192.168.1.120',
    -> master_user='rep1',
    -> master_password='123123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

正确执行后启动Slave同步进程

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

主从同步检查
mysql> show slave statusG

show slave status中Slave_IO_Running: No

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

解决办法：每个库的uuid应该是不一样的，修改auto.cnf文件(在mysql 的data目录下)的uuid：

[auto]

server-uuid=6dcee5be-8cdb-11e2-9408-90e2ba2e2ea6

按照这个16进制格式，随便改下，重启mysql即可。

mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.120
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin.000008
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：
(1)主数据库进行锁表操作，不让数据再进行写入动作
mysql> FLUSH TABLES WITH READ LOCK;

(2)查看主数据库状态
mysql> show master status;

(3)记录下 FILE 及 Position 的值。
将主服务器的数据文件（整个/opt/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。

(4)取消主数据库锁定
mysql> UNLOCK TABLES;

2.3 验证主从复制效果

主服务器上的操作

mysql> create database cowell;
Query OK, 1 row affected (0.08 sec)
mysql> use database cowell;
ERROR 1049 (42000): Unknown database 'database'
mysql> use cowell;
Database changed
mysql> create table test(id int(3),name char(10));
Query OK, 0 rows affected (0.24 sec)
mysql> insert into test values(001,'mes');
Query OK, 1 row affected (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.11 sec)

在从服务器上查看
mysql> show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cowell             |
| mysql              |
| performance_schema |
| test               |
+--------------------+

5 rows in set (0.01 sec)
=============================
数据库cowell已经自动生成

mysql> use cowell;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | mes  |
+------+------+
1 row in set (0.00 sec)

记录也已经存在

由此，整个MySQL主从复制的过程就完成了。