MySQL的主从复制

- Master：192.168.26.134 (CentOS 7)
- Slave ：192.168.26.132 (CentOS 7)
- Mariadb Server Version：5.5.56

# yum -y install ntpdate
# ntpdate 172.18.0.1 #修改为你本地的时间同步服务器或公共时间同步服务器

# yum -y install mariadb-server
# mysql_secure_installation

[root@master ~]# vim /etc/my.cnf.d/server.cnf 
    [server]
    log_bin=/data/logs/bin-log #修改默认路径，路径名(/data/logs)为二进制日志存放目录，基名(bin-log)为二进制日志前缀，应尽量做到见名知义
    server_id=1 #DB服务器唯一识别id
[root@master ~]# mkdir -pv /data/logs #创建相应目录
[root@master ~]# chown -R mysql:mysql /data/logs #务必授权mysql用户为所有者(组), 否则DB Server将启动失败
[root@master ~]# systemctl restart mariadb

[root@slave ~]# vim /etc/my.cnf.d/server.cnf 
    [server]
    skip_name_resolve=on #忽略主机名解析，可以提升服务器效率
    innodb_file_per_table=on #为 InnoDB 引擎使用独立表空间
    max_connections=20000 #最大并发连接数
    relay_log=/data/logs/relay-log #同上，此处为中继日志前缀
    server_id=2 
[root@slave ~]# mkdir -pv /data/logs #创建相应目录
[root@slave ~]# chown -R mysql:mysql /data/logs #同上，须授权mysql用户方可正常启动服务
[root@slave ~]# systemctl restart mariadb

[root@master ~]# mysql -uroot -p
MariaDB [testdb]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO syncuser@'192.168.26.%' IDENTIFIED BY 'guomai'; #授权syncuser用户读取Master端二进制文件和POS
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> SHOW MASTER STATUS \G #记录二进制文件名和POS值。Slave端基于这2个值实现对Master端的数据同步
*************************** 1. row ***************************
            File: bin-log.000001
        Position: 1704
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

MariaDB [(none)]> help change master to #获取帮助
MariaDB [(none)]> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.26.134',
    -> MASTER_USER='syncuser',
    -> MASTER_PASSWORD='guomai',
    -> MASTER_LOG_FILE='bin-log.000001',
    -> MASTER_LOG_POS=1704;

MariaDB [(none)]> START SLAVE IO_THREAD, SQL_THREAD; #启动IO线程和SQL线程复制Master端数据
Query OK, 0 rows affected (0.00 sec)

[root@master ~]# mysql -uroot -p
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use testdb;
Database changed

MariaDB [testdb]> create table students (
    -> id int primary key,
    -> name varchar(20),
    -> age int
    -> );
Query OK, 0 rows affected (0.75 sec)

MariaDB [testdb]> insert into students values (1,"xiaoqiu","22"),(2,"xiaomu","21"),(3,"guomai","23");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [testdb]> select * from students;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | xiaoqiu |   22 |
|  2 | xiaomu  |   21 |
|  3 | guomai  |   23 |
+----+---------+------+
3 rows in set (0.00 sec)

MariaDB [testdb]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [testdb]> use testdb;
Database changed
MariaDB [testdb]> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| students         |
+------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> SELECT * FROM students; #如下，可以确定主从复制成功。在Master端执行的任何操作都在在Slave端同步呈现。
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | xiaoqiu |   22 |
|  2 | xiaomu  |   21 |
|  3 | guomai  |   23 |
+----+---------+------+
3 rows in set (0.00 sec)