mysql主从复制部署

• 主从复制的用途：

  • 实时灾备，用于故障切换
    
    
    
  • 读写分离，提供查询服务
    
  • 备份，避免影响业务
    

• 主从复制部署

  • 必要条件

    • 主库开启binlog日志（设置log-bin参数）
    • 主从server-id不同
    • 从库服务器连通主库

  • 步骤

    • 备份还原（mysqldump或xtrabackup）
    • 授权（grant replication slave on .)
    • 配置复制，并启动（change master to )
    • 查看主从复制信息（show slave status\G)

实验环境：
节点1：（主节点）

hostname：miles21
ip      ：192.168.137.21

节点2：（从节点）

hostname：miles22
ip      ：192.168.137.22

备份miles21上的数据

[root@miles21 ~]# mysqldump -uroot -pbeijing --socket=/data/mysql.sock --single-transaction -A --master-data=1 > /home/mysql/backup/all_db.sql
查看备份文件
[root@miles backup]# more all_db.sql 
...
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=528;
...

还原miles21上的数据

[root@miles21 mysql]# mysql -uroot -pbeijing -hmiles22 -P3307
mysql> source /home/mysql/backup/all_db.sql;

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

授权：在miles21上授权一个具有复制权限的用户

[root@miles backup]# mysql -uroot -p --socket=/data/mysql.sock 
mysql> grant replication slave on *.* to repl@'192.168.137.22' identified by 'beijing';
Query OK, 0 rows affected (0.01 sec)

配置复制：在miles22上

[root@miles22 ~]# mysql -uroot -p --socket=/data/mysql.sock
mysql> ? change master to
...
CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

...
#这里的MASTER_LOG_FILE、MASTER_LOG_POS为备份文件中的信息（上文中可看到）
mysql> CHANGE MASTER TO MASTER_HOST='192.168.137.21',MASTER_USER='repl',MASTER_PASSWORD='beijing',MASTER_PORT=3333,MASTER_LOG_FILE='mysql-bin.000013',MASTER_LOG_POS=528;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
#启动复制
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
#查看主从复制信息
mysql> show slave status\G
...
#表示主从复制OK
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

检验主从复制
在miles21上进行操作

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| test          |
+---------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | m1   |
|    2 | m2   |
+------+------+
2 rows in set (0.00 sec)

mysql> insert into test values (3,'m3'),(4,'m4');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

在miles22上查看

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | m1   |
|    2 | m2   |
|    3 | m3   |
|    4 | m4   |
+------+------+
4 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

在miles21上

mysql> drop database db2;
Query OK, 0 rows affected (0.00 sec)

在miles22上查看

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