16.MySQL主从同步

1.主从同步原理

主从复制原理.jpg

2.主服务器配置

[root@mariadb_master ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log-bin
[root@mariadb_master ~]# mysql -uroot -p
Enter password: 
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'stone'@'192.168.177.%' IDENTIFIED BY'stone';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       351 |
| mysql-bin.000002 |      1869 |
| mysql-bin.000003 |       342 |
+------------------+-----------+

3.从服务器配置

[root@mariadb_slave ~]# vim /etc/my.cnf
[mysqld]
server-id=2
read-only
[root@mariadb_master ~]# mysql -uroot -p
Enter password:
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.177.133',
MASTER_USER=stone,
MASTER_PASSWORD='stone',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000003',
MASTER_LOG_POS=342;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G;

4.验证同步效果

主服务器

MariaDB [(none)]> create database db1;    
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

从服务器

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)