MySQL 主从复制

参考链接
1、环境：
IP：
192.168.1.10 主
192.168.1.11 从
系统版本：
centos 7
软件：
mariadb
2、配置文件修改
主库修改：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

character-set-server=utf8
lower_case_table_names=1
autocommit = 1
log-bin=mysql-bin       #表示启用二进制日志
server-id=10            #表示server编号，编号要唯一

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[client]
port        = 3306
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

###
新增内容：
[mysqld]
character-set-server=utf8
lower_case_table_names=1
autocommit = 1
log-bin=mysql-bin       #表示启用二进制日志
server-id=10            #表示server编号，编号要唯一
[client]
port        = 3306
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8
###

从库修改：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

character-set-server=utf8
lower_case_table_names=1
autocommit = 1
server-id=11            #表示server编号，编号要唯一

[client]
port        = 3306
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

###
新增内容：
[mysqld]
character-set-server=utf8
lower_case_table_names=1
autocommit = 1
log-bin=mysql-bin       #表示启用二进制日志
server-id=11            #表示server编号，编号要唯一
[client]
port        = 3306
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8
###

3、配置主从结构
在主服务器上创建复制数据的账号并授权

grant replication slave on *.* to 'copy'@'%' identified by '123456';

查看主服务器状态 默认初始值：
File：mysql-bin.000001
Position：154

show master status;

如果不是初始值，是因为设置了copy用户导致Position数据增加 需要重置一下状态：

reset master;

4、从服务器配置

show slave status;
stop slave; 	#停止复制，相当于终止从服务器上的IO和SQL线程
reset slave;

# 设置从服务器的master
change master to master_host='192.168.1.10',master_user='copy',
master_port=3306,master_password='123456',
master_log_file='mysql-bin.000001',master_log_pos=154;

# 执行开始复制
start slave;
# 查看主从复制是否搭建完成
show slave status \G

如果 Slave_IO_Running: 为no 执行如下命令
stop slave;
reset slave;
start slave;

5、测试

# 主
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> create table t_user(id int not null);
Query OK, 0 rows affected (0.01 sec)
# 从
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+
5 rows in set (0.01 sec)
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| t_user           |
+------------------+
1 row in set (0.00 sec)