主从复制原理:
从库生成两个线程,一个i/o线程,一个SQL线程;
i/o线程去请求主库的binlog,并且得到的binlog日志写到relay log(中继日志)文件中,
主库会生成一个log dump线程,用来给从库的i/o线程传binlog;
SQL线程,会读取中继日志文件,解析成具体的操作并执行,从而达到主从数据库数据同步。
环境说明:
数据库角色 | IP | 系统版本与应用 | 有无数据 |
---|---|---|---|
主数据库 | 192.168.225.128 | redhat7 mysql-5.7 | 有数据 |
从数据库 | 192.168.225.129 | redhat7 mysql-5.7 | 无数据 |
MySQL主从配置
//在主中创建一个库,并查看
mysql> create database hxd;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hxd |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.31 sec)
//再查看从库中有哪些库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
//全备主库
//全备主库时
需要另开一个终端,给数据库加上读锁,避免在备份期间有其他人在写入导致数据不一样
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
//此锁表的终端必须在备份完成后才能退出
[root@hxdserver ~]# mysqldump -uroot -phxd123456. --all-databases > /opt/all-201811091118.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@hxdserver ~]# scp /opt/all-201811091118.sql root@192.168.225.129:/opt/
root@192.168.225.129's password:
all-201811091118.sql 100% 783KB 68.1MB/s 00:00
//解除主库的锁表状态,直接退出交互式界面即可
mysql> exit
Bye
//在从库上恢复主库的备份并查看从库有哪些库,确保与主库一致
[root@localhost ~]# mysql -uroot -phxd123456. < /opt/all-201811091118.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -phxd123456. -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| hxd |
| mysql |
| performance_schema |
| sys |
+--------------------+
- 在两台数据库都没有数据或数据完全相同的情况下,直接做以下操作
- 在主数据库里创建一个同步账号授权给从库使用
mysql> create user 'repl'@'192.168.225.129' identified by 'repl123.';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.225.129';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
- 配置主数据库
[root@hxdserver ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql-bin //启用binlog日志
server-id=1 //数据库服务器唯一标识符,主库的id值必须比从库的小
//重启服务
[root@hxdserver ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
//查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 配置从数据库
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=2 //设置从库的唯一标识符,从库的id值必须比主库大
relay-log=mysql-relay-bin //启用中继日志relay-log
//重启服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL... SUCCESS!
//在从数据库配置并开启主从复制
mysql> change master to
-> master_host='192.168.225.128',
-> master_user='repl',
-> master_password='repl123.',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
//master_log_file='主库状态中的File'
//master_log_pos='主库状态中的Position'
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.225.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //必须为yes
Slave_SQL_Running: Yes //必须为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: 154
Relay_Log_Space: 527
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
Master_UUID: 2092b848-e3f4-11e8-85e8-000c294b7cb8
Master_Info_File: /opt/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
- 验证
- 在主数据库的hxd库中创建表并插入数据
mysql> use hxd;
Database changed
mysql> create table student(id int not null,name varchar(10) not null,age tinyint);
Query OK, 0 rows affected (0.04 sec)
mysql> insert student values(1,'tom',15),(2,'jerry',25);
Query OK, 2 rows affected (1.67 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 15 |
| 2 | jerry | 25 |
+----+-------+------+
2 rows in set (0.00 sec)
//在从数据库查看数据是否同步了
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hxd |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use hxd;
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
mysql> show tables;
+---------------+
| Tables_in_hxd |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
mysql> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 15 |
| 2 | jerry | 25 |
+----+-------+------+
2 rows in set (0.00 sec)