数据库主从复制 如何配置MySQL数据库主从复制
大家好，今天小编来为大家解答数据库主从复制这个问题，如何配置MySQL数据库主从复制很多人还不知道，现在让我们一起来看看吧！
如何配置MySQL数据库主从复制
MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护日志文件的一个索引以跟踪日志循环。当一个从服务器连接到主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知下一次更新。
为什么使用主从复制？
1、主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为备份。
2、通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。但是不要同时在主从服务器上进行更新，这样可能引起冲突。
3、使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。
MySQL使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让主服务器发送二进制日志。主服务器创建一个线程将二进制日志中的内容发送到从服务器。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，从服务器使用此线程读取中继日志并执行日志中包含的更新。SHOW PROCESSLIST语句可以查询在主服务器上和从服务器上发生的关于复制的信息。
默认中继日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是从服务器主机名，nnnnnn是序列号。用连续序列号来创建连续中继日志文件，从000001开始。从服务器跟踪中继日志索引文件来识别目前正使用的中继日志。默认中继日志索引文件名为 host_name-relay-bin.index。在默认情况，这些文件在从服务器的数据目录中被创建。中继日志与二进制日志的格式相同，并且可以用 mysqlbinlog读取。当SQL线程执行完中继日志中的所有事件后，中继日志将会被自动删除。
从服务器在数据目录中另外创建两个状态文件--master.info和relay-log.info。状态文件保存在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。
设置主从复制：
1、确保在主服务器和从服务器上安装的MySQL版本相同，并且最好是MySQL的最新稳定版本。
2、在主服务器上为复制设置一个连接账户。该账户必须授予REPLICATION SLAVE权限。如果账户仅用于复制(推荐这样做)，则不需要再授予任何其它权限。
mysql> GRANT REPLICATION SLAVE ON*.*
-> TO'replication'@'%.yourdomain.com' IDENTIFIED BY'slavepass';
3、执行FLUSH TABLES WITH READ LOCK语句清空所有表和块写入语句：
mysql> FLUSH TABLES WITH READ LOCK；
保持mysql客户端程序不要退出。开启另一个终端对主服务器数据目录做快照。
shell> cd/usr/local/mysql/
shell> tar-cvf/tmp/mysql-snapshot.tar./data
如果从服务器的用户账户与主服务器的不同，你可能不想复制mysql数据库。在这种情况下，应从归档中排除该数据库。你也不需要在归档中包括任何日志文件或者master.info或relay-log.info文件。
当FLUSH TABLES WITH READ LOCK所置读锁定有效时（即mysql客户端程序不退出)，读取主服务器上当前的二进制日志名和偏移量值：
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File| Position| Binlog_Do_DB| Binlog_Ignore_DB|
+---------------+----------+--------------+------------------+
| mysql-bin.003| 73| test| manual,mysql|
+---------------+----------+--------------+------------------+
File列显示日志名，而Position显示偏移量。在该例子中，二进制日志值为mysql-bin.003，偏移量为73。记录该值。以后设置从服务器时需要使用这些值。它们表示复制坐标，从服务器应从该点开始从主服务器上进行新的更新。
如果主服务器运行时没有启用--logs-bin，SHOW MASTER STATUS显示的日志名和位置值为空。在这种情况下，当以后指定从服务器的日志文件和位置时需要使用的值为空字符串('')和4.
取得快照并记录日志名和偏移量后，回到前一中端重新启用写活动：
mysql> UNLOCK TABLES；
4、确保主服务器主机上my.cnf文件的[mysqld]部分包括一个log-bin选项。该部分还应有一个server-id=Master_id选项，其中master_id必须为1到232–1之间的一个正整数值。例如：
[mysqld]
log-bin
server-id=1
如果没有提供那些选项，应添加它们并重启服务器。
5、停止从服务器上的mysqld服务并在其my.cnf文件中添加下面的行：
[mysqld]
server-id=2
slave_id值同Master_id值一样，必须为1到232–1之间的一个正整数值。并且，从服务器的ID必须与主服务器的ID不相同。
6、将数据备据目录中。确保对这些文件和目录的权限正确。服务器 MySQL运行的用户必须能够读写文件，如同在主服务器上一样。
Shell> chown-R mysql:mysql/usr/local/mysql/data
7、启动从服务器。在从服务器上执行下面的语句，用你的系统的实际值替换选项值：
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;
8、启动从服务器线程：
mysql> START SLAVE；
执行这些程序后，从服务器应连接主服务器，并补充自从快照以来发生的任何更新。
9、如果出现复制错误，从服务器的错误日志（HOSTNAME.err）中也会出现错误消息。
10、从服务器复制时，会在其数据目录中发现文件master.info和HOSTNAME-relay-log.info。从服务器使用这两个文件跟踪已经处理了多少主服务器的二进制日志。不要移除或编辑这些文件，除非你确切知你正在做什么并完全理解其意义。即使这样，最好是使用CHANGE MASTER TO语句。
如何设置MySQL的主从复制
本篇文章给大家介绍的是关于在MySQL服务器之间设置主从复制，下面我们来看具体内容。
设置细节：
主服务器：192.168.1.10
从服务器：192.168.1.20
数据库：mydb
1.设置MySQL主服务器
在主服务器上创建一个具有REPLICATION SLAVE权限的mysql帐户，复制客户端将连接到master。
mysql> GRANT REPLICATION SLAVE ON*.* TO'repl_user'@'192.168.1.20' IDENTIFIED BY'secretpassword';
mysql> FLUSH PRIVILEGES;在所有表上都有block write语句，因此不要在备份后进行更改。
mysql> use mydb;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> exit;编辑mysql配置文件并在[mysqld]部分下添加以下代码。
# vim/etc/my.cnf[mysqld]
log-bin=mysql-bin
binlog-do-db=mydb
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1重新启动master mysql服务器以使更改生效。
# service mysqld restart使用以下命令检查当前二进制日志文件名（File）和当前偏移量（Position）值。
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File| Position| Binlog_Do_DB| Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
| mysql-bin.000002| 107| mydb||
+------------------+----------+--------------+------------------+以上输出显示当前二进制文件使用的是mysql-bin.000002，偏移值为107。记下这些值以在从属服务器上使用。
备份数据库并将其复制到slave mysql server。
# mysqldump-u root-p mydb> mydb.sql
# scp mydb.sql 192.168.1.20:/opt/完成备份后，从表中删除READ LOCK，以便进行更改。
mysql> UNLOCK TABLES;2.设置MySQL Slave Server
编辑salve mysql配置文件并在[mysqld]部分下添加以下值。
# vim/etc/my.cnf[mysqld]
server-id=2
replicate-do-db=mydbserver-id始终为非零数值。这些值永远不会与其他主服务器和从服务器相似。
重启mysql slave server，如果你已经配置了复制，请在启动时使用-skip-slave-start，不要立即连接到主服务器。
#/etc/init.d/mysqld restart使用以下命令在从属服务器上设置选项值。
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.10',
-> MASTER_USER='repl_user',
-> MASTER_PASSWORD='secretpassword',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=107;最后启动从属线程
mysql> SLAVE START;检查从服务器的状态。
mysql> show slave status G*************************** 1. row***************************
Slave_IO_State:
Master_Host: 192.168.1.15
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: mydb
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: 107
Relay_Log_Space: 107
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: NULL
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
1 row in set(0.00 sec)
mysql>MySQL主从复制已在你的系统和工作模式下成功配置。
mysql怎么实现主从复制
mysql主从复制的配置
1.准备工作
有两台MySQL数据库服务器Master和slave，Master为主服务器，slave为从服务器。
初始状态时，Master和slave中的数据信息相同，当Master中的数据发生变化时，slave也跟着发生相应的变化，使得master和slave的数据信息同步，达到备份的目的。
要点：
负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。
因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。
环境：
Master和slave的MySQL数据库版本同为5.6.28
操作系统：CentOS release 6.5(Final)
IP地址：master:120.77.153.204 slave:120.77.148.74
2. master的配置
（1）创建复制帐号
mysql> CREATE USER'masterslave'@'120.77.148.74' IDENTIFIED BY'123456';Query OK, 0 rows affected(0.00 sec)
（2）主从复制授权
GRANT REPLICATION SLAVE,RELOAD,SUPER ON*.* TO'masterslave'@'120.77.148.74' IDENTIFIED BY'123456';
（3）拷贝数据
（假如是你完全新安装mysql主从服务器，这一步就不需要。因为新安装的master和slave有相同的数据）
关停Master服务器，将Master中的数据拷贝到B服务器中，使得Master和slave中的数据同步，并且确保在全部设置操作结束前，
禁止在Master和slave服务器中进行写操作，使得两数据库中的数据一定要相同！
3.修改配置文件
对master进行配置，包括打开二进制日志，指定唯一的servr ID
[root@myserver~]# find/-type f-name my.cnf/usr/my.cnf[root@myserver~]# vim/usr/my.cnfserver_id=1log-bin=mysql-bin
4.重启master，运行SHOW MASTER STATUS，输出如下：
[root@myserver~]# service mysql stopShutting down MySQL.... SUCCESS! [root@myserver~]# service mysql startStarting MySQL. SUCCESS! [root@myserver~]# mysql-uroot-pEnter password: Welcome to the MySQL monitor. Commands end with; or g.Your MySQL connection id is 3Server version: 5.6.28-log MySQL Community Server(GPL)Copyright(c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type'help;' or'h' for help. Type'c' to clear the current input statement.mysql> mysql> show master status; mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File| Position| Binlog_Do_DB| Binlog_Ignore_DB| Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001| 120||||+------------------+----------+--------------+------------------+-------------------+1 row in set(0.00 sec)mysql>
配置slave
1.修改my.cnf文件
[root@wenhaijin mysql-5.6.28]# find/-type f-name my.cnf/usr/my.cnf[root@wenhaijin mysql-5.6.28]# vim/usr/my.cnf log_bin= mysql-binserver_id= 2relay_log= mysql-relay-binlog_slave_updates= 1read_only= 1
server_id是必须的，而且唯一。
slave没有必要开启二进制日志，但是在一些情况下，必须设置。
例如，如果slave为其他slave的master，必须设置bin_log。
在这里，我们开启了二进制日志，而且显示了命名（默认名称为hostname，但是，如果hostname改变则会出现问题）。
relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志（后面会看到它的用处）。
有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。
所以，尽量使用read_only，它防止改变数据（除了特殊的线程）。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。
2.启动slave
接下来就是让slave连接master，并开始重做master二进制日志中的事件。
你不应该用配置文件进行该操作，而应该使用CHANGE MASTER TO语句，该语句可以完全取代对配置文件的修改，而且它可以为slave指定不同的master，而不需要停止服务器。如下：
mysql> CHANGE MASTER TO MASTER_HOST='120.77.153.204',-> MASTER_USER='masterslave',-> MASTER_PASSWORD='123456',-> MASTER_LOG_FILE='mysql-bin.000001',-> MASTER_LOG_POS=0;Query OK, 0 rows affected, 2 warnings(0.01 sec)
MASTER_LOG_POS的值为0，因为它是日志的开始位置。
你可以用SHOW SLAVE STATUS语句查看slave的设置是否正确：
mysql> SHOW SLAVE STATUSG*************************** 1. row*************************** Slave_IO_State: Master_Host: 120.77.153.204 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 4 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No 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: 4 Relay_Log_Space: 120 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: NULLMaster_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: 0 Master_UUID: Master_Info_File:/var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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: 01 row in set(0.00 sec)
Slave_IO_State, Slave_IO_Running,和Slave_SQL_Running是No
表明slave还没有开始复制过程。日志的位置为4而不是0，这是因为0只是日志文件的开始位置，并不是日志位置。
实际上，MySQL知道的第一个事件的位置是4。
为了开始复制，你可以运行START SLAVE；
mysql> START SLAVE; mysql> SHOW SLAVE STATUSG;*************************** 1. row*************************** Slave_IO_State: Waiting for master to send event Master_Host: 120.77.153.204 Master_User: masterslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1762 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 1925 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: Skip_Counter: 0 Exec_Master_Log_Pos: 1762 Relay_Log_Space: 2098 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: 0Master_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: 4df9a165-03fd-11e7-b234-00163e0243e8 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 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: 01 row in set(0.00 sec)
在这里主要是看：
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。
日志的位置增加了，意味着一些事件被获取并执行了。
如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。
你可通过show processlistG；查看master和slave上线程的状态。在master上，你可以看到slave的I/O线程创建的连接。
3.授权masterslave用户远程登入，然后使用navicat远程连接
在master的test数据库中创建user表。
然后在slave中进行刷新发现user表已经同步过来。
刷新前：
刷新后：
在master的test库中user表添加一条数据。
然后打开slave中test库的user表，发现该数据也被同步过来了。
说明已经成功实现mysql的主从复制。
数据库主从复制和如何配置MySQL数据库主从复制的问题分享结束啦，以上的文章解决了您的问题吗？欢迎您下次再来哦！