数据库集群的主从复制模型完整实现
短信预约 -IT技能 免费直播动态提醒
主从配置过程：
参看：
https://mariadb.com/kb/en/library/setting-up-replication/
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html主服务器192.168.27.7配置：
[root@master ~]$vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table
log_bin
server-id=1
[root@master ~]$systemctl start mariadb.service
MariaDB [(none)]> show binary logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
1 row in set (0.00 sec)
[root@master ~]$mysql < hellodb_InnoDB.sql
MariaDB [(none)]> show binary logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 7655 |
+--------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.27.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)从服务器192.168.27.17服务配置：
从服务器上的配置：
[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.
innodb_file_per_table
server_id=2
注意：默认的server_id是0；
MariaDB [(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 0 |
+---------------+-------+
1 row in set (0.01 sec)
查看帮助：
MariaDB [(none)]> help change master to;
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.27.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000001',
-> MASTER_LOG_POS=245,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.27.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: No 线程未启动；
Slave_SQL_Running: No 线程未启动；
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_Master_Log_Pos: 245 复制的起始位置；
Relay_Log_Space: 245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No 可以启用加密；
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)中继日志已经创建；
[root@centos7x ~]$ll /var/lib/mysql/
total 28724
-rw-rw---- 1 mysql mysql 16384 Feb 25 06:37 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Feb 25 06:37 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Feb 25 06:37 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Feb 25 06:37 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Feb 25 05:09 ib_logfile1
-rw-rw---- 1 mysql mysql 264 Feb 25 05:15 mariadb-bin.000001
-rw-rw---- 1 mysql mysql 21 Feb 25 05:10 mariadb-bin.index
-rw-rw---- 1 mysql mysql 245 Feb 25 06:38 mariadb-relay-bin.000001
-rw-rw---- 1 mysql mysql 27 Feb 25 06:38 mariadb-relay-bin.index
-rw-rw---- 1 mysql mysql 84 Feb 25 06:38 master.info
drwx------ 2 mysql mysql 4096 Feb 25 05:09 mysql
srwxrwxrwx 1 mysql mysql 0 Feb 25 06:37 mysql.sock
drwx------ 2 mysql mysql 4096 Feb 25 05:09 performance_schema
-rw-rw---- 1 mysql mysql 52 Feb 25 06:38 relay-log.info
drwx------ 2 mysql mysql 6 Feb 25 05:09 test启动从服务器上的复制线程：
MariaDB [(none)]> start slave;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.27.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 245 读取的日志的位置；
Relay_Log_File: mariadb-relay-bin.000005
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
Slave_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_Master_Log_Pos: 245 执行的日志的位置；
Relay_Log_Space: 1113
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 落后的时间差；
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)主服务器的读写操作：
主服务器的日志位置；
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 8217 |
| mariadb-bin.000002 | 245 |
+--------------------+-----------+
2 rows in set (0.00 sec)
主服务器的写：
MariaDB [(none)]> create database wangdb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 8217 |
| mariadb-bin.000002 | 332 |
+--------------------+-----------+
2 rows in set (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.27.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 332
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 618
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
Slave_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_Master_Log_Pos: 332
Relay_Log_Space: 9153
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)从服务器：
从服务器瞬间同步：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
| wangdb |
+--------------------+
6 rows in set (0.00 sec)
停止从服务器；并在主服务器上更新操作；
MariaDB [(none)]> create database wangdb2;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database wangdb3;
Query OK, 1 row affected (0.00 sec)
从服务器；
[root@centos7x ~]$systemctl stop mariadb.service
[root@centos7x ~]$
[root@centos7x ~]$
[root@centos7x ~]$systemctl start mariadb.service
unknown [(none)]> show databases;
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
| wangdb |
| wangdb2 |
| wangdb3 |
+--------------------+
8 rows in set (0.00 sec)
从服务器上的relay-log.info记录的是
[root@centos7x ~]$ls /var/lib/mysql/
aria_log.00000001 ibdata1 mariadb-relay-bin.000004 master.info performance_schema wangdb
aria_log_control ib_logfile0 mariadb-relay-bin.000005 mysql relay-log.info wangdb2
hellodb ib_logfile1 mariadb-relay-bin.index mysql.sock test wangdb3
[root@centos7x ~]$
[root@centos7x ~]$cat /var/lib/mysql/relay-log.info
./mariadb-relay-bin.000005
709
mariadb-bin.000002
510
8
[root@centos7x ~]$cat /var/lib/mysql/master.info
18
mariadb-bin.000002
510
192.168.27.7
repluser
centos
3306
60
0
0
1800.000
0
主服务器；
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 8217 |
| mariadb-bin.000002 | 510 |
+--------------------+-----------+
2 rows in set (0.00 sec)免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/52.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
