Mysql-5.7.20主从复制测试[20180110]
程序员文章站
2022-07-02 16:05:23
前言 Mysql 5.7.20测试主从复制 环境 主库 192.168.1.59 t-xi-sonar01 从库 192.168.1.51 t-xi-orc01 设定主机host文件 主库 [root@t-xi-sonar01 ~]# cat /etc/hosts 127.0.0.1 localho ......
前言
Mysql 5.7.20测试主从复制
环境
主库 192.168.1.59 t-xi-sonar01
从库 192.168.1.51 t-xi-orc01
设定主机host文件
【Slave-Server】
主库 [root@t-xi-sonar01 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.1.59 t-xi-sonar01 192.168.1.51 t-xi-orc01
从库 [root@t-xi-orc01 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.1.51 t-xi-orc01 192.168.1.59 t-xi-sonar01Mysql数据库配置 【Master-Server】
[root@t-xi-sonar01 ~]# service mysqld stop Stopping mysqld: [ OK ] [root@t-xi-sonar01 ~]# vim /etc/my.cnf #Server ID，一般设置成IP地址的最后一位，如下测试就按后两位 server_id=59 #开启log bin，名字最好有意义用来区分 log-bin=dev-bin #需要进行复制的数据库，可以指定数据库 #binlog-do-db=DB_master #不需要备份的数据库，可以设置多个数据库，一般不会同步mysql这个库 binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema #为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存 binlog_cache_size=1m #二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。 expire_logs_days=7 # 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。 # 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致 slave_skip_errors=1062 [root@t-xi-sonar01 ~]# service mysqld start Starting mysqld: [ OK ] [root@t-xi-sonar01 ~]# mysql 5.7.20登陆报错解决ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
mysql -u root
update user set authentication_string=PASSWORD("****") where User='root';
flush privileges;
主库创建同步账户
service mysqld start
mysql> mysql -u root -p
mysql> CREATE USER 'replication'@'192.168.1.51' IDENTIFIED BY 'slave';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.51';
mysql> flush privileges;
主库锁定后备份将资料同步到从库
mysql>use sonar
mysql>FLUSH TABLES WITH READ LOCK;
mysqldump -u root -p --databases sonar > sonar.sql
scp sonar.sql @192.168.1.51:/root
mysql> unlock tables;
[master-server]
mysql> show master status ;
+----------------+----------+--------------+---------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+---------------------------------------------+-------------------+
| dev-bin.000004 | 783 | | mysql,information_schema,performance_schema | |
+----------------+----------+--------------+---------------------------------------------+-------------------+
【Slave-Server】
service mysqld stop
vim /etc/my.cnf
#add slave-server
server_id=51
#binlog-ignore-db=mydql
#binlog-ignore-db=information_schema
#binlog-ignore-db=performance_schema
#log-bin=dev-slave-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=dev-relay-bin
#log_slave_updates=1
read_only=1
service mysqld start
将主库备份导入从库
mysql>source /root/sonar.sql
添加链接到主库同步复制的账户
mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.59', MASTER_USER='replication', MASTER_PASSWORD='slave', MASTER_LOG_FILE='dev-bin.000001', MASTER_LOG_POS=0;
MASTER_LOG_FILE：指定log bin日志文件名称
MASTER_LOG_POS ：指定同步复制log分区号，可以从0开始。
查看slave状态
show slave status \G
Slave_IO_State #从站的当前状态
Slave_IO_Running： Yes #读取主程序二进制日志的I/O线程是否正在运行
Slave_SQL_Running： Yes #执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样
Seconds_Behind_Master #是否为0，0就是已经同步了
启动slave
start slave;
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.59
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dev-bin.000004
Read_Master_Log_Pos: 1255
Relay_Log_File: dev-relay-bin.000005
Relay_Log_Pos: 1464
Relay_Master_Log_File: dev-bin.000004
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: 1255
Relay_Log_Space: 1878
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: 59
Master_UUID: d6901902-ea28-11e7-b859-000c29255261
Master_Info_File: /var/lib/mysql/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)
【Master-Server】
创建table和database测试
在sonar下建立测试表
mysql> use sonar;
mysql> create table slave_t(
-> id int(10) not null, name varchar(20)
-> )
-> ;
Query OK, 0 rows affected (1.57 sec)
mysql> insert into slave_t values(1,'name01');
Query OK, 1 row affected (0.33 sec)
创建slave_db测试数据库
mysql> use mysql
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> create database slave_db;
Query OK, 1 row affected (0.17 sec)
【Slave-Server】
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | slave_db | | sonar | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> use sonar; Database changed mysql> select * from slave_t; +----+--------+ | id | name | +----+--------+ | 1 | name01 | +----+--------+ 1 row in set (0.00 sec)
主库上的table和database已同步复制过来
【命令参考】
查看主库master状态
mysql> show master status;
+----------------+----------+--------------+---------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+---------------------------------------------+-------------------+
| dev-bin.000004 | 1426 | | mysql,information_schema,performance_schema | |
+----------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)
查看从库主机列表
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 51 | | 3306 | 59 | 86fff1d0-f62d-11e7-834d-000c29477dac |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
查看bin log文件列表
mysql> show binary logs;
+----------------+-----------+
| Log_name | File_size |
+----------------+-----------+
| dev-bin.000001 | 177 |
| dev-bin.000002 | 177 |
| dev-bin.000003 | 177 |
| dev-bin.000004 | 1426 |
+----------------+-----------+
4 rows in set (0.00 sec)
查看bin log文件的内容
mysql> show binlog events;
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| dev-bin.000001 | 4 | Format_desc | 59 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| dev-bin.000001 | 123 | Previous_gtids | 59 | 154 | |
| dev-bin.000001 | 154 | Stop | 59 | 177 | |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec)
mysql> show binlog events in 'dev-bin.000004';
+----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+
| dev-bin.000004 | 4 | Format_desc | 59 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| dev-bin.000004 | 123 | Previous_gtids | 59 | 154 | |
| dev-bin.000004 | 154 | Anonymous_Gtid | 59 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| dev-bin.000004 | 219 | Query | 59 | 417 | CREATE USER 'replication'@'192.168.1.51' IDENTIFIED WITH 'mysql_native_password' AS '*51125B3597BEE0FC43E0BCBFEE002EF8641B44CF' |
| dev-bin.000004 | 417 | Anonymous_Gtid | 59 | 482 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| dev-bin.000004 | 482 | Query | 59 | 631 | GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.51' |
| dev-bin.000004 | 631 | Anonymous_Gtid | 59 | 696 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| dev-bin.000004 | 696 | Query | 59 | 783 | flush privileges |
| dev-bin.000004 | 783 | Anonymous_Gtid | 59 | 848 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| dev-bin.000004 | 848 | Query | 59 | 985 | use `sonar`; create table slave_t(
id int(10) not null, name varchar(20)
) |
| dev-bin.000004 | 985 | Anonymous_Gtid | 59 | 1050 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| dev-bin.000004 | 1050 | Query | 59 | 1123 | BEGIN |
| dev-bin.000004 | 1123 | Table_map | 59 | 1177 | table_id: 326 (sonar.slave_t) |
| dev-bin.000004 | 1177 | Write_rows | 59 | 1224 | table_id: 326 flags: STMT_END_F |
| dev-bin.000004 | 1224 | Xid | 59 | 1255 | COMMIT /* xid=178006 */ |
| dev-bin.000004 | 1255 | Anonymous_Gtid | 59 | 1320 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| dev-bin.000004 | 1320 | Query | 59 | 1426 | create database slave_db |
+----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+
17 rows in set (0.13 sec)
查看当前数据库线程列表
【master-server】
mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
Id: 199
User: root
Host: localhost
db: mysql
Command: Query
Time: 0
State: starting
Info: SHOW PROCESSLIST
*************************** 2. row ***************************
Id: 201
User: replication
Host: t-xi-orc01:41452
db: NULL
Command: Binlog Dump
Time: 1450
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 3. row ***************************
Id: 203
User: sonar
Host: localhost:57162
db: sonar
Command: Sleep
Time: 434
State:
Info: NULL
*************************** 4. row ***************************
Id: 204
User: sonar
Host: localhost:57358
db: sonar
Command: Sleep
Time: 123
State:
Info: NULL
*************************** 5. row ***************************
Id: 205
User: sonar
Host: localhost:57524
db: sonar
Command: Sleep
Time: 2
State:
Info: NULL
*************************** 6. row ***************************
Id: 206
User: sonar
Host: localhost:57720
db: sonar
Command: Sleep
Time: 3
State:
Info: NULL
6 rows in set (0.00 sec)
【slave-server】
mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
Id: 4
User: root
Host: localhost
db: sonar
Command: Query
Time: 0
State: starting
Info: SHOW PROCESSLIST
*************************** 2. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 1445
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 26717
State: Slave has read all relay log; waiting for more updates
Info: NULL
3 rows in set (0.00 sec)
从库启动复制
mysql> START SLAVE;
从库停止复制
mysql> STOP SLAVE;
参考： https://segmentfault.com/a/1190000010867488 https://www.jianshu.com/p/208667156faf
下一篇： jQuery电梯楼层实现