MySQL高可用(三)搭建主备同步实战

目标

掌握如何搭建一主一备的主备架构

实验环境

  • 系统:Ubuntu 18.04.1 LTS
  • MySQL版本:5.7.32
  • 主库IP:192.168.200.120
  • 备库IP:192.168.200.121

操作步骤

1.分别在两台服务器上安装MySQL

sudo apt-get install mysql-server
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev

注:要求MySQL版本号一致

2.(主库)配置同步账号

创建数据库复制用户:

mysql> CREATE USER 'repl'@'192.168.200.121' IDENTIFIED BY '123456';

授权:

mysql> grant replication slave on *.* to 'repl'@'192.168.200.121';

3.(主库)修改/etc/my.cnf文件,注意确保:server-id 唯一

[mysqld]
bind-address = 192.168.200.120 //这里的IP地址必须是通过ipconfig查出来的IP
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = empw_test
#binlog_ignore_db = include_database_name
binlog_format = row # binlog的格式 row 具有 statement和mixed 不具备的优势 填写 row
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1 # 和 sync_binlog 组成双1 配置 保证数据的完整性

4.(主库)重启master端

service mysql restart

5.(主库)查询主库上当前的二进制日志名和偏移量值,记录下file跟position的值,待会从库要用

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

6.(从库)修改/etc/my.cnf文件,注意确保:server-id 唯一

[mysqld]
server-id = 2

7.(从库)重启slave端

service mysql restart

8.(从库)对从数据库服务器做相应设置,指定复制使用的用户,主数据库服务器的IP、端口以及开始执行复制的日志文件和位置等

mysql> CHANGE MASTER TO MASTER_HOST='192.168.200.120',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=154;

9.(从库)启动slave线程

mysql> start slave;

10.主库跟从库分别检查状态

检查master的:

mysql> show processlist;
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host                  | db   | Command     | Time | State                                                         | Info             |
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  2 | root | localhost             | NULL | Query       |    0 | starting                                                      | show processlist |
|  3 | repl | 192.168.200.121:56822 | NULL | Binlog Dump |   12 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

看到上面的Command: Binlog Dump说明配置成功!

检查slave的:

mysql> show slave status;

提示: Slave_IO_RunningSlave_SQL_Running 两个值为YES基本上成功了。

posted @ 2020-12-23 10:28  大杂草  阅读(451)  评论(1编辑  收藏  举报