mysql主从skip1677_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)