mysql主备同步搭建_MySQL高可用（三）搭建主备同步实战

目标

掌握如何搭建一主一备的主备架构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版本号一致sql

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的：rest

mysql> show slave status;

提示: Slave_IO_Running 和 Slave_SQL_Running 两个值为YES基本上成功了。日志