mysql主从复制部署

数据库 4个月前 admin
161 0
  • 主从复制的用途:
    • 实时灾备,用于故障切换
    • 读写分离,提供查询服务
    • 备份,避免影响业务
  • 主从复制部署
    • 必要条件
      • 主库开启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)
版权声明:admin 发表于 2020-11-06 20:28:47。
转载请注明:mysql主从复制部署 | 小爱网址导航

暂无评论

暂无评论...