这篇博客详细介绍了MySQL的主从复制配置过程和实验步骤。从主节点的二进制日志设置，到从节点的中继日志及复制线程启动，再到具体的实验环境和操作，包括开启binlog、设置serverid、数据备份与导入等。同时，文中还提到了主从复制中应注意的事务安全问题，如设置read_only和sync_binlog参数。
MySQL主从复制：
配置说明：
主节点：
1）启动二进制日志；
2）为当前节点设置一个全局唯一的ID号；
3）创建有复制权限的用户账号；
从节点：
1）启动中继日志；
2）为当前节点设置一个全局唯一的ID号；
3）使用有复制权限的用户账号连接至主服务器，并启动复制线程；
流程：
首先，当用户对master数据库进行数据修改的时候，主节点MySQL服务器会把用户的数据写入到磁盘中，并记录到二进制日志中；Slave节点得知Master节点数据库内容发生改变便启用I/O线程去请求Master节点的二进制日志，Master节点会开启自己dump线程并把二进制日志返回给Slave节点的I/O线程，I/O线程接收Master节点的二进制日志文件之后，会把文件放置在自己的中继日志中，并记录二进制日志文件名和二进制日志文件中事件位置点等信息，以便于下一次请求Master节点的二进制日志文件；Slave节点的SQL线程检测到有了中继日志，便会在本地进行执行日志当中的SQL语句，也写入到磁盘中，并等待下一次中继日志的更新；如果Slave节点也开启了二进制日志功能，那么这些执行操作也会记录到Slave节点的二进制日志中，此时，Master节点和Slave节点通过日志文件，执行了相同的SQL语句，在主从复制状态正常的情况下，就可以确保Master节点和Slave节点的数据完全一样。
MySQL主从复制实验：
环境介绍：
Master：192.168.10.10
Slave ：192.168.10.20
1）开启主库的binlog功能和定义serverid
[root@jyy ~]#
oot@jyy ~]# vim /etc/my.cnf
▽
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin
server-id=1
# Disabling symbolic-links is recommended to prevent assorted security ri
sks
symbolic-links=0
2）重启服务，进入数据库创建库和表、授权用户
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> create database aaa;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use aaa;
Database changed
MariaDB [aaa]> create table jyy(id int(10),name varchar(20),age int(10));
Query OK, 0 rows affected (0.00 sec)
MariaDB [aaa]> insert into jyy values(1,'aaa',1),(2,'bbb',2),(3,'ccc',3);Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [aaa]> select * from jyy;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | ccc | 3 |
+------+------+------+
3 rows in set (0.00 sec)
MariaDB [aaa]> grant replication slave on *.* to 'redhat'@'%' identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)
MariaDB [aaa]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [aaa]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 884 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [aaa]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 30325 |
| mysql-bin.000002 | 1038814 |
| mysql-bin.000003 | 884 |
+------------------+-----------+
3 rows in set (0.00 sec)
3）锁库并备份库表数据
MariaDB [aaa]> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
MariaDB [aaa]> exit
Bye
[root@jyy ~]# mysqldump -uroot -p -A -B > all_bak.sql
Enter password:
[root@jyy ~]# ll all_bak.sql
-rw-r--r-- 1 root root 515852 Jan 22 20:09 all_bak.sql
[root@jyy ~]#
MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
4）从库设置serverid并进行认证配置
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=3 //设置Slave节点的server id；
relay-log=relay-log //启动中继日志功能；
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> change master to master_host='192.168.10.10', master_port=3306, master_user='redhat', master_password='redhat', master_log_file='mysql-bin.000003', master_log_pos=884;
Query OK, 0 rows affected (0.11 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]>
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.10
Master_User: redhat
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 884
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes //IO线程Yes状态
Slave_SQL_Running: Yes //SQL线程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: 884
Relay_Log_Space: 825
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)
5）把主库备份的文件拷贝给从库并导入数据
[root@jyy ~]# scp all_bak.sql 192.168.10.20:/root/
The authenticity of host '192.168.10.20 (192.168.10.20)' can't be established.
ECDSA key fingerprint is 8d:b4:bb:88:db:60:52:9d:54:ed:20:69:cd:d4:73:d6.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.10.20' (ECDSA) to the list of known hosts.
root@192.168.10.20's password:
all_bak.sql 100% 504KB 503.8KB/s 00:00
[root@jyy ~]#
MariaDB [(none)]>
MariaDB [(none)]> system ls
a Desktop fdisk.sh panduan.sh Videos
all_bak.sql Documents httpd.sh Pictures
anaconda-ks.cfg Downloads initial-setup-ks.cfg Public
cuowu.sh echo.sh Music Templates
MariaDB [(none)]> source all_bak.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]>
MariaDB [test]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa | //已经有a库
| bbb |
| haha |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [test]> select * from aaa.jyy;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | ccc | 3 |
+------+------+------+ //和主库内容一致
3 rows in set (0.01 sec)
6）在主库插入数据测试：
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> use aaa;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [aaa]> insert into jyy values(4,'ddd',1),(5,'eee',5),(6,'fff',6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
查看：
MariaDB [test]> select * from aaa.jyy; //第一次导入数据时查询结果
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | ccc | 3 |
+------+------+------+
3 rows in set (0.01 sec)
MariaDB [test]> select * from aaa.jyy; //第二次主库数据发生改变时查询结果
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | ccc | 3 |
| 4 | ddd | 1 |
| 5 | eee | 5 |
| 6 | fff | 6 |
+------+------+------+
6 rows in set (0.00 sec)
复制架构中应该注意的问题：
1、限制从服务器为只读
在从服务器上设置read_only=ON;此限制对拥有super权限的用户均无效；
也可以进行锁表：flush tables with read lock；
2、保证主从复制的事务安全
在master节点启用参数：
sync_binlog=ON；
innodb_flush_logs_at_trx_commit=ON //事务提交后，立即刷写到磁盘中；
skip_slave_start=ON //关闭自动启动slave线程；
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
