1、软件环境

环境名称​

Mysql版本​

IP​

OS 版本​

主库​

Mysql8.0.30​

172.16.134.24​

Centos 7.2​

从库​

Mysql8.0.30​

172.16.134.25​

Centos 7.2​


2、主库安装配置

2.1主库配置文件

[mysqld]​

port = 3308​

mysqlx_port=33080​

socket = /u01/mysql3308/data/mysql.sock​

mysqlx_socket = /u01/mysql3308/data/mysqlx.sock​

basedir = /u01/mysql3308/mysql​

datadir = /u01/mysql3308/data​

pid-file = /u01/mysql3308/data/mysql.pid​

tmpdir = /u01/mysql3308/tmp​

### log​

server_id=1​

log_error = /u01/mysql3308/logs/error.log​

slow_query_log = 1​

long_query_time = 1​

slow_query_log_file = /u01/mysql3308/logs/slow.log​

log_timestamps = SYSTEM​

log_bin = /u01/mysql3308/binlog/mysql-bin​

binlog_format = row​

binlog_cache_size = 16M​

binlog_row_image = full​

binlog_rows_query_log_events = 1​


2.2初始化及启动

初始化:如果不是默认的路径，请使用初始参数​

$>mysqld --defaults-file=/u01/mysql3308/my.cnf --initialize --user=mysql​


$>mysqld_safe --defaults-file=/u01/mysql3308/my.cnf &​


2.3创建复制用户

mysql8 授权用户必须先创建，创建和授权不能使用同一语句​

create​user repl@'%' identified with mysql_native_password by 'rep1123';​

grant replication slave on *.* to repl@'%';​

flush privileges;​


3、从库搭建

从库搭建方法有以下多种方法，可自由选择：​

  • 使用mydumper导出主库数据，然后进行导入到从库​
  • 使用FTWL锁库/停库，获得主库位点，然后copy数据文件至从库​
  • 使用xtrabackup进行数据库备份，然后在从库还原​
  • 使用MySQL 8.0的clone特性将实例克隆到从库

   这里采用xtrabackup进行数据库的备份及恢复


3.1主库执行备份

xtrabackup --defaults-file=/u01/mysql3308/my.cnf --user=xtrabk --password=xtrabk --socket=/u01/mysql3308/data/mysql.sock --backup --target-dir=/u01/mysql3308/backup/full_xtra


3.2从库基础环境搭建

3.2.1 mysql soft 复制

打包mysql软件并复制到从库解压，删除数据目录、binglog、logs文件​


3.2.2从库配置文件

编辑从库my.cnf 文件，替换为从库的路径​

server_id=2​

relay_log=/u01/mysql3309/relaylog/mysql-relay-bin

read-only=on


3.2.3 从库恢复

准备：应用redo，类似于之前的apply​

xtrabackup --defaults-file=/u01/mysql3309/my.cnf --prepare --target-dir=/u01/mysql3308/backup/full_xtra​

恢复：​

xtrabackup --defaults-file=/u01/mysql3309/my.cnf ---copy-back --target-dir=/u01/mysql3308/backup/full_xtra​

恢复完成后，更改data、binlog目录权限​


3.3 启动从库

Mysqld_safe –defaults-file=/u01/mysql3309/my.cnf &​


3.4 获取二进制文件及位置

[root@test01]#cat xtrabackup_info ​

binlog_pos = filename 'mysql-bin.000009', position '157'​


3.5 添加指向主库相关配置信息

Mysql>change replication source to ​

source_host='172.16.134.24',​

source_port=3308,​

source_user='repl',​

source_password='rep1123',​

source_log_file='mysql-bin.000009',​

source_log_pos=157;​


3.6 开启从库复制

Mysql>start replica;​


3.7 查看复制状态

mysql> show replica status \G​

*************************** 1. row ***************************​

Replica_IO_State: Waiting for source to send event​

Source_Host: 172.16.134.24​

Source_User: repl​

Source_Port: 3308​

Connect_Retry: 60​

Source_Log_File: mysql-bin.000009​

Read_Source_Log_Pos: 2909​

Relay_Log_File: mysql-relay-bin.000002​

Relay_Log_Pos: 3078​

Relay_Source_Log_File: mysql-bin.000009​

Replica_IO_Running: Yes​

Replica_SQL_Running: Yes​

Replicate_Do_DB: ​

Replicate_Ignore_DB: ​

Replicate_Do_Table: ​

Replicate_Ignore_Table: ​

Replicate_Wild_Do_Table: ​

Replicate_Wild_Ignore_Table: ​

Last_Errno: 0​

Last_Error: ​

Skip_Counter: 0​

Exec_Source_Log_Pos: 2909​

Relay_Log_Space: 3288​

Until_Condition: None​

Until_Log_File: ​

Until_Log_Pos: 0​

Source_SSL_Allowed: No​

Source_SSL_CA_File: ​

Source_SSL_CA_Path: ​

Source_SSL_Cert: ​

Source_SSL_Cipher: ​

Source_SSL_Key: ​

Seconds_Behind_Source: 0​

Source_SSL_Verify_Server_Cert: No​

Last_IO_Errno: 0​

Last_IO_Error: ​

Last_SQL_Errno: 0​

Last_SQL_Error: ​

Replicate_Ignore_Server_Ids: ​

Source_Server_Id: 1​

Source_UUID: f1b88047-a5ea-11ed-8ee1-246e9657f7a0​

Source_Info_File: mysql.slave_master_info​

SQL_Delay: 0​

SQL_Remaining_Delay: NULL​

Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates​

Source_Retry_Count: 86400​

Source_Bind: ​

Last_IO_Error_Timestamp: ​

Last_SQL_Error_Timestamp: ​

Source_SSL_Crl: ​

Source_SSL_Crlpath: ​

Retrieved_Gtid_Set: ​

Executed_Gtid_Set: ​

Auto_Position: 0​

Replicate_Rewrite_DB: ​

Channel_Name: ​

Source_TLS_Version: ​

Source_public_key_path: ​

Get_Source_public_key: 0​

Network_Namespace: ​

1 row in set (0.00 sec)​


3.8验证复制状态

主库创建及更新表​

mysql> select * from test;​

+------+------+​

| id | name |​

+------+------+​

| 1 | wang |​

| 2 | lei |​

+------+------+​

2 rows in set (0.00 sec)​


mysql> create table test_01 select * from test;​

Query OK, 2 rows affected (0.48 sec)​

Records: 2 Duplicates: 0 Warnings: 0​


mysql> select * from test_01;​

+------+------+​

| id | name |​

+------+------+​

| 1 | wang |​

| 2 | lei |​

+------+------+​

2 rows in set (0.00 sec)​


mysql> insert into test_01 values(3,'jk');​

Query OK, 1 row affected (0.15 sec)​


mysql> select * from test_01;​

+------+------+​

| id | name |​

+------+------+​

| 1 | wang |​

| 2 | lei |​

| 3 | jk |​

+------+------+​

3 rows in set (0.00 sec)​

mysql>​

从库查询并验证​

mysql> select * from test_01;​

+------+------+​

| id | name |​

+------+------+​

| 1 | wang |​

| 2 | lei |​

| 3 | jk |​

+------+------+​

3 rows in set (0.00 sec)​

mysql>​


至此，一个简单的主从环境搭建完成，需要注意的时MySQL 8.0.23前后复制命令的差别。​