【MySQL】1托2 ab复制 一个主机两个slave操作手册
2024-09-29 01:53:54
所有实验环境全部是新建的,如果不是新建的mysql一定要备份!!!
环境:CentOS release 6.8 x64
master:192.168.25.100
slave1: 192.168.25.101
slave2: 192.168.25.102
1.查看是否开启二进制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| binarylog.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
如果有回显,证明已经开启二进制日志
如果显示为:
mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
则证明需要手动将其开启
开启过程如下:
修改/etc/my.cnf文件
添加如下内容:
log-bin=/usr/local/mysql/binarylog //开启二进制日志
server-id=1 //需要执行server-id才能启动成功,5.7以下版本不需要指定
sync_binlog=1
innodb_flush_log_at_trx_commit=1 //这两条是优化参数
修改完成后重启mysql服务,查看是否开启
这里还有一个细节,如果数据库里以前就应用了二进制日志,那么建议做一次重置
2.创建一个数据库在master上,并将该库进行备份
mysql> create database zhang;
Query OK, 1 row affected (0.00 sec)
mysql> use zhang;
Database changed
mysql> create table zhang(id int,name varchar(10));
Query OK, 0 rows affected (0.26 sec)
mysql> insert into zhang values(1,"zhang");
Query OK, 1 row affected (0.11 sec)
mysql> insert into zhang values(2,"da");
Query OK, 1 row affected (0.11 sec)
mysql> select * from zhang;
+------+-------+
| id | name |
+------+-------+
| 1 | zhang |
| 2 | da |
+------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zhang |
+--------------------+
5 rows in set (0.00 sec)
备份该数据库
mysqldump -uroot -p -B mysql zhang> zhang.sql //-B这个选项是备份多个数据库 这里备份了mysql数据库和zhang数据库
3.在slave1上查看是否有和我这个库重名的库,如果有就备份后删除掉,如果没有的话,直接将zhang.sql导入到slave1中
将master备份的zhang.sql传输到slave1服务器上。
导入到slave1中
scp ~/zhang.sql 192.168.25.101:~/
mysql -uroot -p < zhang.sql
登陆到slave1上的mysql查看是否导入成功;
[root@test6_101 ~]# mysql -uroot -p
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zhang |
+--------------------+
5 rows in set (0.00 sec)
4.在master服务器端创建用户 test来传输二进制日志
mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'192.168.25.%' IDENTIFIED BY 'test123';
查看二进制日志号
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: binarylog.000001
Position: 1325
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
5.在slave端配置:
5.1 配置my.cnf文件
[mysqld]
log_bin = /usr/local/mysql/binarylog //启动SQL日志,并指定文件名前缀
server_id = 2 //指定服务器ID号,不要与Master的相同
log_slave_updates=1 //记录从库更新,便于实现“主-从-从”链式复制
sync_binlog=1
innodb_flush_log_at_trx_commit=1 //这两条是优化参数
完成过后创建路径mkdir /usr/local/mysql/binarylog -p
更改完成后,重启mysql数据库
5.2 登陆mysql数据库中发起同步操作
mysql> change master to master_host='192.168.25.100', //master的主机ip
-> master_user='test', //拥有replication slave权限的用户
-> master_password='test123', //用户密码
-> master_log_file='binarylog.000001', //日志文件的名称,可以根据上述的mysql> SHOW MASTER STATUS\G查看
-> master_log_pos=1325; //这个日志pos可以从show master status查看
Query OK, 0 rows affected, 2 warnings (0.28 sec)
5.3 启动复制功能
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
5.4查看状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.25.100
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binarylog.000002
Read_Master_Log_Pos: 694
Relay_Log_File: test6_101-relay-bin.000006
Relay_Log_Pos: 860
Relay_Master_Log_File: binarylog.000002
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: 694
Relay_Log_Space: 1071
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: 55681c8d-a1be-11e8-8cdb-000c2936b376
Master_Info_File: /usr/local/mysql/mydata/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)
5.5测试
在master上执行一条更新或者插入一条数据,在slave上查看是否同步
主:
mysql> use zhang;
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> insert into zhang values(3,"zhangda");
Query OK, 1 row affected (0.11 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| binarylog.000002 | 423 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binary logs
-> ;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| binarylog.000001 | 1348 |
| binarylog.000002 | 423 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> insert into zhang values(4,"zhangdada");
Query OK, 1 row affected (0.01 sec)
从:
mysql> use zhang;
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> select * from zhang;
+------+-----------+
| id | name |
+------+-----------+
| 1 | zhang |
| 2 | da |
| 3 | zhangda |
| 4 | zhangdada |
+------+-----------+
4 rows in set (0.00 sec)
证明已经可以同步数据了~~
6.下面来操作slave2
6.1 在master重新备份一个sql文件
传到slave2上
scp ~/zhang.sql 192.168.25.102:~/
应用该sql
[root@test6_102 ~]# mysql -uroot -p < zhang.sql
Enter password:
[root@test6_102 ~]# mysql -uroot -p
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zhang |
+--------------------+
5 rows in set (0.00 sec)
6.2 配置my.cnf文件
[mysqld]
log_bin = /usr/local/mysql/binarylog //启动SQL日志,并指定文件名前缀
server_id = 3 //指定服务器ID号,不要与Master的相同
log_slave_updates=1 //记录从库更新,便于实现“主-从-从”链式复制
sync_binlog=1
innodb_flush_log_at_trx_commit=1 //这两条是优化参数
完成过后创建路径
mkdir /usr/local/mysql/binarylog -p
更改完成后,重启mysql数据库
6.3 登陆mysql数据库中发起同步操作
mysql> change master to master_host='192.168.25.100', //master的主机ip
-> master_user='test', //拥有replication slave权限的用户
-> master_password='test123', //用户密码
-> master_log_file='binarylog.000001', //日志文件的名称,可以根据上述的mysql> SHOW MASTER STATUS\G查看
-> master_log_pos=1325; //这个日志pos可以从show master status查看
Query OK, 0 rows affected, 2 warnings (0.14 sec)
6.4 启动复制功能
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
6.5 查看状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.25.100
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binarylog.000002
Read_Master_Log_Pos: 694
Relay_Log_File: test6_101-relay-bin.000006
Relay_Log_Pos: 860
Relay_Master_Log_File: binarylog.000002
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: 694
Relay_Log_Space: 1071
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: 55681c8d-a1be-11e8-8cdb-000c2936b376
Master_Info_File: /usr/local/mysql/mydata/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)
检验一下看数据是否同步
完成
7 后续操作
slave端/etc/my.cnf
因为是备份库,所以建议将备库设置为read only read_only=1
还有一个参数是report_host=
在master输入:SHOW SLAVE HOSTS 这个参数设置后,可以在master端查看有哪些slave已经开始工作
参考:http://blog.51cto.com/xujpxm/1386300
最新文章
- 国外干货!6个方法助你设计出优秀的APP
- salesforce 零基础开发入门学习(四)多表关联下的SOQL以及表字段Data type详解
- 【前端福利】用grunt搭建自动化的web前端开发环境-完整教程
- regsvr32的使用
- MVC 的各个部分都有那些技术来实现?如何实现?
- Http 请求
- Ejabberd源码解析前奏--配置
- Javascript函数中传递带空格的参数
- 《ACM国际大学生程序设计竞赛题解I》——6.10
- java中文乱码解决之道(二)—–字符编码详解:基础知识 + ASCII + GB**
- IDEA的配置
- 在eclipse中配置android ndk的自动编译环境builders
- 1.3 正则表达式和python语言-1.3.4使用 match()方法匹配字符串
- ES5 &; ES6 基础
- Altium Designer 使用中的小技巧1
- docker in docker 出现 libltdl.so.7 问题
- CentOS是哪个版本 CentOS版本信息查看技巧
- 浅谈个人对RAID技术的理解
- System.ComponentModel.DataAnnotations.Schema 冲突
- VIM技巧:选择文本块
热门文章
- easyui form提交时验证必填,打开时不显示必填提示
- 七、TestNG传递参数2
- vue seo 优化
- vue第十五单元(熟练使用vue-router插件)
- 【Python】自动化测试的7个步骤
- burpsuite 2020.12.1最新版蓝色版,下载安装破解
- [UWP] - 修改应用程序在任务栏上的显示Logo
- 【磁盘/文件系统】第二篇:标准磁盘分区流程针对fdisk(硬盘容量小于2T且分区数不能大于15个分区)
- 【JAVA并发第二篇】Java线程的创建与运行,线程状态与常用方法
- 采购订单写入sap失败后,抛出自定义异常,回滚数据库