mysql的主从复制和单向异步复制:
mysql的AB复制：
mysql 数据库的版本,两个数据库版本要相同,或者 slave 比 master 版本高!
# yum install mysql mysql-server -y
master server 配置
1)配置 /etc/my.cnf 配置文件
在[mysqld]下添加一下参数
log-bin=mysql-bin #启动二进制日志系统
binlog-do-db=test #二进制需要同步的数据库名,如果需要同步多个库,例如要再同步 westos
库,再添加一行“binlog-do-db=westos”,以此类推
server-id=1
#必须为 1 到 232–1 之间的一个正整数值
binlog-ignore-db=mysql #禁止同步 mysql 数据库
2 ) 启动 master
# service mysqld start
3)创建同步帐户,并给予权限
mysql> GRANT REPLICATION SLAVE ON *.* to ginkgo@'172.25.28.2' identified by 'redhat';
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 854 | test,ginkgo | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
slave1server 配置
1 )配置 /etc/ my.cnf 文件
在[mysqld]下添加一下参数
server-id=2
#从服务器 ID 号,不要和主 ID 相同,如果设置多个从服务器,每个从服务器必
须有一个唯一的 server-id 值,必须与主服务器的以及其它从服务器的不相同。
可以认为 server-id 值类似于 IP 地址:这些 ID 值能唯一识别复制服务器群集
中的每个服务器实例。
2 ) 启动 slave
# service mysqld start
3) 在 slave1 上执行一下命令
mysql> change master to master_host='172.25.28.1',master_user='ginkgo',master_password='redhat',master_log_file='mysql-bin.000005',master_log_pos=420;
Query OK, 0 rows affected (0.05 sec)
mysql> slave start;
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: 172.25.28.1
Master_User: ginkgo
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 420
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
如果都是 yes,表示从库的 I/O,Slave_SQL 线程都正确开启.表明数据库正在同步
[root@server1 mysql]# ls
backup2.db ibdata1 mysql-bin.000001 mysql-bin.000005
backup.db ib_logfile0 mysql-bin.000002 mysql-bin.index
dashboard_production ib_logfile1 mysql-bin.000003 mysql.sock
ginkgo mysql mysql-bin.000004
[root@server1 mysql]#
相关文件作用:
1. mysql-bin.index: 服务器一旦开启二进制日志,会产生一个与二日志文件同名,但是以.index 结尾
的文件。它用于跟踪磁盘上存在哪些二进制日志文件。MySQL 用它来定位二进制日志文件。
2. mysqld-relay-bin.index: 该文件的功能与 mysql-bin.index 类似,但是它是针对中继日志,而不是
二进制日志。
3. master.info :保存 master 的相关信息。不要删除它,否则,slave 重启后不能连接 master。
4. relay-log.info :包含 slave 中当前二进制日志和中继日志的信息如果写操作较少,而读操作很多时,可以采取这种结构。你可以将读操作分布到其它的 slave,从而减小
master 的压力。但是,当 slave 增加到一定数量时,slave 对 master 的负载以及网络带宽都会成为一个严重的问题。这种结构虽然简单,但是,它却非常灵活,足够满足大多数应用需求
当设置 log_slave_updates 时,你可以让 slave 扮演其它 slave 的 master。此时,slave 把 SQL 线程执行的事件写进行自己的二进制日志(binary log),然后,其它的 slave 可以获取这些事件并执行它,从而有效缓解master 的压力
添加一个 slave2:
1. 由于 master 上已经有数据,而新加的 slave2 没有,必须在配置复制前同步数据。
1)在 master 上执行一下命令
mysql> FLUSH TABLES WITH READ LOCK;
#锁表
[root@server1 mysql]# mysqldump --all-databases --lock-all-tables >backup.db
#备份
mysql> UNLOCK TABLES;
#表解锁
或：
[root@server1 mysql]# mysqlbinlog mysql-bin.000005
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170310 14:30:57 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.71-log created 170310 14:30:57 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
...
# at 106
#170310 15:17:06 server id 1 end_log_pos 258 Querythread_id=4exec_time=0error_code=0
SET TIMESTAMP=1489130226/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
mysqlbinlog --start-datetime=”2017-03-10 14:30:57 ” mysql-bin-00005 > backup.sql
scp backup.db 172.25.28.3
[root@server1 mysql]# scp backup.db 172.25.28.3:
2. 在 slave1 上加入以下设置:
# vi /etc/my.cnf
....
server-id=2
log-bin=mysql-bin
binlog-ignore-db=mysqlbinlog-do-db=test
log-slave-updates
....
# /etc/init.d/mysqld restart
3. 在 slave1 上创建同步帐户,并给予权限
mysql> grant replication slave on *.* to ginkgo@'172.25.28.3' identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 333
Binlog_Do_DB: test,ginkgo
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 333 | test,ginkgo | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
4. 在 slave2 上添加配置:
# vi /etc/my.cnf
server-id=3
# /etc/init.d/mysqld start
5. 在 slave2 上执行一下命令
mysql> change master to master_host = '172.25.28.2',master_user='ginkgo',master_password='redhat',master_log_file='mysql-bin.000001',master_log_pos=333;
Query OK, 0 rows affected (0.04 sec)
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: 172.25.28.2
Master_User: ginkgo
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 333
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
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:
高版本的主从复制：
搭建mysql的主从复制：
确保系统上没有低版本的的mysql软件及其文件：
rpm -qa|grep mysql
清除所有的安装mysql，rm -fr /var/lib/mysql
首先下载tar包，编译安装mysql
mysql5.7启动后会在系统自动生成一个复杂的密码，在其日志文件中记录着，通过
grep password /var/log/mysqlog抓取密码，然后登陆，登陆后第一件事是更改密码使用
mysql> alter user root@'localhost' identified by 'Ginkgo+007';
Query OK, 0 rows affected (0.05 sec)
然后即可使用自己设定的密码登陆，对于slave作同样的事情。
修改配置文件文件:
[root@server1 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=ginkgo
binlog-ignore-db=mysql
gtid-mode=ON/打开gtid模式
enforce-gtid-consistency=1
#
[root@server2 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
server-id=2
gtid-mode=ON
enforce-gtid-consistency=1
#
重新启动mysql服务（配置文件作出更改后要重起服务，除非使用热修改，即使用mysql语句修改，在生产中一般都是使用sql语句进行。）
在server1.example.com 中：
对从服务器授权：
mysql> grant replication slave on *.* to ginkgo@'172.25.33.2' identified by 'Ginkgo+007';
Query OK, 0 rows affected, 1 warning (0.06 sec)
在server2.example.com中添加主服务器：
mysql> change master to master_host='172.25.33.1',master_user='ginkgo',master_password='Ginkgo+007',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.53 sec)
启动slave
在server2上执行：
start slave;
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.33.1
Master_User: ginkgo
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 601
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 814
Relay_Master_Log_File: mysql-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: 601
Relay_Log_Space: 1023
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: f8c6f53c-08ab-11e7-b323-5254000b91d2
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: f8c6f53c-08ab-11e7-b323-5254000b91d2:1-2
Executed_Gtid_Set: f8c6f53c-08ab-11e7-b323-5254000b91d2:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
在master上添加数据库，观察从上的动静：
mysql> create database ginkgo;
Query OK, 1 row affected (0.05 sec)
mysql> create table ginkgo.userdb;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table ginkgo.userdb(
-> username varchar(20) not null,
-> password varchar(20) not null);
Query OK, 0 rows affected (0.42 sec)
mysql> desc ginkgo.userdb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
在server2.example.com中执行
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ginkgo |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.33.1
Master_User: ginkgo
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 992
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 1205
Relay_Master_Log_File: mysql-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: 992
Relay_Log_Space: 1414
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: f8c6f53c-08ab-11e7-b323-5254000b91d2
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: f8c6f53c-08ab-11e7-b323-5254000b91d2:1-4
Executed_Gtid_Set: f8c6f53c-08ab-11e7-b323-5254000b91d2:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
没有错误。
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 7 | system user | | NULL | Connect | 1305 | Waiting for master to send event | NULL |
| 8 | system user | | NULL | Connect | 489 | Slave has read all relay log; waiting for more updates | NULL |
| 11 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
