MySQL设置主从复制
主从复制:
单向,双向,环形,级联,一主多从
创新互联服务项目包括永福网站建设、永福网站制作、永福网页制作以及永福网络营销策划等。多年来,我们专注于互联网行业,利用自身积累的技术优势、行业经验、深度合作伙伴关系等,向广大中小型企业、政府机构等提供互联网行业的解决方案,永福网站推广取得了明显的社会效益与经济效益。目前,我们服务的客户以成都为中心已经辐射到永福省份的部分城市,未来相信会继续扩大服务区域并继续获得客户的支持与信任!
双机复制的5种情形
1、异步主从(默认常规)
2、双写(前段程序对两个数据库同时写,必须两边都落实,程序才返回成功)
3、利用外挂软件实现实时主库Binlog日志抓取,从而可以在当机的时候补全从库
4、谷歌开发的半同步插件
5、DRBD
主从读写分离
1、通过程序实现(性能,效率最佳,推荐)
php,java等程序可以通过设置多个连接文件轻松实现主从读写分离。
2、通过软件实现读写分离
MySQL-proxy, Amoeba等代理软件也可以实现读写分离功能,但是最好还是程序实现。
3、开发dbproxy
读写分离逻辑图展示:
主从同步原理:
实际上是异步的,即总是主库写完日志,日志才可能被从库应用。
master slave
线程:IO 线程:IO/SQL
开启log-bin 需要设置的启动参数:CHANGE MASTER TO
怎删改会去写log-bin MASTER_HOST='192.168.1.111'
MASTER_PORT=3308
MASTER_USER='alrinrep'
MASTER_PASSWORD='password123'
MASTER_LOG_FILE='mysql-bin.000047'
MASTER_LOG_POS='5632'
开启同步方式:start slave
从库IO向主库发起复制请求,主库开始对从库认证,通过后主库IO根据从库
日志要求开始发送bin-log到从库relay-log。一个周期后,从库更新master.info
,并重新向主库发起请求。
SQL线程从relay-log发现新数据,并写到slave的数据文件和日志系统。
相关语句:--master-data=1,
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=1191;
建立相关的账号,即alrinrep
并授予应有的权限
MySQL主从复制原理过程
1、Slave服务器上执行start slave,开启主从复制开关
2、Slave服务器的IO线程会通过在Master上授权的复制用户权限请求连接Master服务器,并请求从指定的bin-log日志文件指定位置
(change master to 命令相关参数)之后发送binlog日志
3、Master服务器接受到来自Slave服务器的IO线程的请求后,Master服务器上负责复制IO线程根据Slave服务器的IO线程请求的信息
读取指定binlog日志文件指定位置之后的binlog日志信息,然后返回给Slave端的IO线程。返回的信息中除了binlog日志的内容外,
还有本次返回日志内容后在Master服务器端的新的binlog文件名称以及在binlog中的下一个指定更新位置
4、当Slave服务器的IO线程获取到来自Master服务器上的IO线程发送的日志以及日志文件及位置点后,将binlog日志内容一次写入到
slave端的Relay log(中继日志)文件(MySQL-relay-bin.xxxxxx)的最后端,并将新的binlog文件名和位置记录到master-info文件
中,以便下一次读取Master端新Binlog日志时能够告诉Master服务器需要从新binlog日志的那个文件哪个文件开始新的binlog内容。
5、Slave服务器端的SQL线程会实时的检测本地relay log中新增的日志内容,然后及时把LOG文件中的内容解析成MASTER端曾经执行过
的SQL语句,并在自身SLAVE服务器上安顺序执行这些SQL。应用玩后清理应用过的日志。
6,经过了上述过程,就可以确保MASTER和SLAVE执行了同样的SQL。在复制正常的情况下,MASTER和SLAVE数据是完全一致的。
主从复制上机实验:
0,环境:3308/3309同机,IP为192.168.199.151
1,修改my.cnf,检查server-id和log-bin,主从server-id必须不同。主库必须开启bin-log
2,添加复制账号和对应的 replication slave 权限
mysql> select user,host,password from user;
+-------+-----------+-------------------------------------------+
| user | host | password |
+-------+-----------+-------------------------------------------+
| root | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B |
| alrin | % | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B |
+-------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> grant replication slave on *.* to alrinrep@'192.168.199.%' identified by 'password123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
3,主库上做备份。先锁表,再备份
mysql> flush table with read lock;
Query OK, 0 rows affected (0.03 sec
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 423 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
新开session窗口后执行备份:
[root@localhost backup]# mysqldump -uroot -ppassword123 -S /data/mysqldata/3308/mysql.sock -A -B --events --master-data=2| gzip > /data/mysqldata/backup/rep.sql.gz
备份后检查:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 423 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
解锁表:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql>
4,从库操作
mysql> change master to
-> MASTER_HOST='192.168.199.151',
-> MASTER_PORT=3308,
-> MASTER_USER='alrinrep',
-> MASTER_PASSWORD='password123',
-> MASTER_LOG_FILE='mysql-bin.000004',
-> MASTER_LOG_POS=423;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
5,开启复制
mysql> start slave;
Query OK, 0 rows affected (0.25 sec)
6,检查slave状态:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.199.151
Master_User: alrinrep
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 423
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 283
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: 423
Relay_Log_Space: 456
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: 16b758c5-2455-11e9-8fc6-080027339667
Master_Info_File: /data/mysqldata/3309/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
mysql>
7,尝试在主库DDL,检查复制状态
主库:
mysql> create database alrin;
Query OK, 1 row affected (0.00 sec)
从库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| alrin |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
命令汇总:
1,grant replication slave on . to alrinrep@'192.168.199.%' identified by 'password123';
2,flush table with read lock;
3,show master status;
4,unlock tables;
5,change master to
MASTER_HOST='192.168.199.151',
MASTER_PORT=3308,
MASTER_USER='alrinrep',
MASTER_PASSWORD='password123',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=423;
6,start slave;
7,show slave status\G
8, 关于log_slave_updates:
默认情况下,从库的SLAVE SQL读了replay_bin日志并应用后,数据库就有了内容,但是并没有去写自己的BINLOG,也就是说,如果slave要再去拖一个slave,所谓的级联复制,是会失败的,因为第二个slave会去读第一个slave的binlog。那么有什么办法让第一个SLAVE写binlog呢,办法就是开启log_slave_updates。
文章标题:MySQL设置主从复制
文章源于:http://pwwzsj.com/article/ghedeg.html