MySQL主从服务器配置.doc-道客多多

资源描述

1、MySQL 主从服务器配置一、同步原理说明Mysql 的 Replication 是一个异步的复制过程，从一个 Mysql instace(我们称之为 Master)复制到另一个 Mysql instance(我们称之 Slave)。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成，其中两个线程(Sql 线程和 IO 线程)在 Slave 端，另外一个线程(IO 线程)在 Master 端。要实现 MySQL 的 Replication ，首先必须打开 Master 端的 Binary Log(mysql-bin.xxxxxx)功能，否则无法实现。因为整个复制过程实

2、际上就是 Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使用“log-bin” 参数选项，或者在 f 配置文件中的 mysqld 参数组 (mysqld标识后的参数部分)增加“log-bin” 参数项。MySQL 复制的基本过程如下：1. Slave 上面的 IO 线程连接上 Master，并请求从指定日志文件的指定位置 (或者从最开始的日志) 之后的日志内容;2. Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO线程根

3、据请求信息读取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置;3. Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log 文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的 Master 端的 bin-log 的文件名和位置记录到 master-info 文件中，以便在下一次读取的时候能够清楚的高速 Master“我需要从某个 bin-log 的哪个

4、位置开始往后的日志内容，请发给我”4. Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。这样，实际上就是在 Master 端和 Slave 端执行了同样的 Query，所以两端的数据是完全一样的。二、设置 mysql 主从配置的优点：1. 解决 web 应用系统，数据库出现的性能瓶颈，采用数据库集群的方式来实现查询负载；一个系统中数据库的查询操作比更新操作要多得多，通过多台查询服务器将数据库的查询分担到不同的查询服务器上从而提高查询效率

5、。2. Mysql 数据库支持数据库的主从复制功能，使用主数据库进行数据的插入、删除与更新操作，而从数据库则专门用来进行数据查询操作，这样可以将更新操作和查询操作分担到不同的数据库上，从而提高了查询效率。三、同步设置基本步骤( 以下设置均涉及 f 文件的修改 )3.1 设置主服务器:启用 bin-log 日志、同步帐号server-id = n #设置 IDlog-bin #启用 bin-log 记录，如需要指定 binlog 文件名，可使用如下语法#log-bin=binlog #binlog 文件名为 binlog.0，binlog.1set-variable=binlog-ignore-

6、db=mysql #不记录数据库 mysql 的更新日志binlog-do-db = #记录指定的数据库的更新日志，还可以单独处理只更新表内容此外，需要注意两个环境变量：innodb_flush_log_at_trx_commit=1 #Default: 1。在使用了事务的数据库中，强烈建议该值显式的设置为 1，以确保在每个事务提交时，日志缓冲被写到日志文件，并将该写操作同步到磁盘上sync_binlog=1 #在每进行 1 次 binlog 写操作后就将该操作同步到硬盘上。设为更高的值可以有效降低 io 并获得更好的性能，但会增加 bilog 丢失的风险为从服务器上的同步帐号授权：mysql

7、GRANT REPLICATION SLAVE ON *.* TO 同步帐号的用户名从服务器 ip地址 IDENTIFIED BY 同步帐号的密码;如果想要从服务器上有权限执行”LOAD TABLE FROM MASTER”和”LOAD DATA FROM MASTER”还需要增加”FILE”和”SELECT”权限;3.2 设置从服务器:设置需要同步的内容server-id = n #设置 IDmaster-host = #设置主服务器的 ipmaster-user = #设置同步帐户的用户名master-password = #设置同步帐户的密码master-port = #设置 TCPIP

8、端口，默认为 3306set-variable=replicate-ignore-db=mysql #设置忽略的同步数据库名set-variable=replicate-db-db= #设置同 pass_adm 步的数据库名若需要单独设置可以针对表级设置的数据多个，请设置多次这里的设置可以在后面登陆进从数据库之后用 CHANGE MASTER TO 来修改。3.3 拷贝主服务器数据到从服务器3.3.1 方式 1:停止主服务器或其中一台从服务器并拷贝数据在需要拷贝数据的服务器上停止 mysqld，完全停止服务。如果是从服务器，则需要在停止 mysqld 之前，停止同步进程。mysqlstop

9、slave;$mysqladmin -uroot -p shutdown确认 mysqld 停止之后，将其中的数据文件 ib*，master.info 需要同步的数据库目录和其他相关文件拷贝到新机器上。binlog 和 relay-log 不用拷贝3.3.2 方式 2：在主服务器或其中一台从服务器上设置锁，禁止一切读写操作并拷贝数据在主服务器上执行:mysqlFLUSH TABLES WITH READ LOCK;导出数据到从数据库(可以使用 tar、scp、mysqldump 等方式，此处实现比较灵活):$cd /mysql/$tar zcvf var.tar.gz var/ #数据目录可能

10、不为 var 目录然后是拷贝此 var.tar.gz 包到从服务器并解压缩到相应目录在主服务器上执行:mysqlUNLOCK TABLES;说明：拷贝主服务器的数据到从服务器的实现方式太多：可以直接停止 mysqld 程序拷贝数据、可以直接 tar 方式打包处理、可以直接 scp 方式处理、可以直接导出相应表结构等，具体实现方式看具体应用，适而择之，主要保证在拷贝数据的时候没有数据写入和更改即可3.4 启动同步启动从数据库的 mysqld,推荐使用-skip-slave-start 启动，进入 mysql 后再 start slave 启动同步。说明：通常使用的 mysqld_safe 会

11、记住启动时的参数，例如-skip-slva-start。如果从库因为意外原因重启，且该数据库启动的时候使用了 mysqld_safe -skip-slave-start，则需要手工启动同步进程，否则同步进程会始终处于停止状态从服务器上执行：mysqlSHOW SLAVE STATUS若 Slave_IO_Running 和 Slave_SQL_Running 两行的值都为 “Yes“，这表明 Slave 的 I/O 和 SQL 线程都在正常运行。说明：推荐使用 CHANGE MASTER TO 语句进行同步参数设置 ; 具体参照 help change master to,语法如下：mast

12、er_def:aster_def , master_def .master_def:MASTER_HOST = host_name MASTER_USER = user_name MASTER_PASSWORD = password MASTER_PORT = port_num MASTER_CONNECT_RETRY = count MASTER_LOG_FILE = master_log_name MASTER_LOG_POS = master_log_pos RELAY_LOG_FILE = relay_log_name RELAY_LOG_POS = relay_log_pos MAS

13、TER_SSL = 0|1 MASTER_SSL_CA = ca_file_name MASTER_SSL_CAPATH = ca_directory_name MASTER_SSL_CERT = cert_file_name MASTER_SSL_KEY = key_file_name MASTER_SSL_CIPHER = cipher_list 四、实例参考:4.1 主数据库 A 的 f 配置：server-id = 1 #设置 IDlog-bin #启用 bin-log 记录binlog-do-db = iknow #记录指定库的 binlog 更新记录，每个数据库一行binlog-

14、do-db = iknow_admbinlog-do-db = .现在，新搭建一个从数据库 B。需要进行如下步骤4.2.修改配置文件: 修改 f，加入如下项：server-id = 2 #指定 server id，每台机器的 id 必须保证唯一log-bin #启用 binlog 记录master-connect-retry = 60 #连接主库的重试间隔master-host = xxx.xxx.xxx.xxx #指定主库 A 的 IP，不能用虚 IPmaster-port = 3306 #指定主库端口master-user = rep #指定同步用户的用户名为 replicationmas

15、ter-password = rep #指定同步用户的密码为 PassWORdreplicate-do-db = iknow #指定需要同步的库，每个库独占一行replicate-do-db = iknow_admreplicate-do-db = .log-slave-updates #在从库上对同步进行日志记录，磁盘占用量较大，建议从库运行稳定后将其去除，如要作为第二层主数据库，则此项必须这里的设置可以在后面登陆进从数据库之后用 CHANGE MASTER TO 来修改。4.3.拷贝数据 :从一台从数据库 C 拷贝数据到 B 从数据库没有对外提供服务，因此可以直接停掉从数据库拷贝数据。注意

16、：停从数据库的时候需要显式的执行 stop slave，并执行 show slave status，确认Read_Master_Log_Pos 和 Exec_Master_Log_Pos 一致，再执行 mysqladmin -uroot -p shutdown 停掉 mysqld。mysqlstop slave;mysqlshow slave status确认 Read_Master_Log_Pos 和 Exec_Master_Log_Pos 一致$ mysqladmin -uroot -p shutdown待 mysqld 完全退出后，将 var 目录下的 ib*，master.info，以

17、及需要同步的库 cp 到本地目录 var.$DATE 下（主要是为了缩短 C 上 mysqld 的服务停止时间）$ cd $MYSQL/var; mkdir /var.$DATE$ cp -r ib* iknow iknow_adm master.info /var.$DATE/$ scp $MYSQL/var.$DATE B:/mysql4.4.在主数据库上为一台从库授权同步mysqlGRANT REPLICATION SLAVE ON *.* TO replicationB 机器的 ip 地址 IDENTIFIED BY Password;4.5.在 B 上启动 mysqld$ mysql

18、d_safe -skip-slave-start 以上语句各项含义如下：MASTER_HOST 主库的机器名或 IP 地址MASTER_USER 主库为从库授权的同步帐号MASTER_PASSWORD 主库为从库授权的同步帐号密码MASTER_LOG_FILE 主库的 binlog 文件名MASTER_LOG_POS 主库的 binlog 文件偏移MASTER_LOG_FILE 和 MASTER_LOG_POS 两项需要在启动主库之前执行 show master status 记录下来，并且必须保证准确无误，否则会出现同步失败的情况。五、监控服务器的状态5.1.监控主服务器的状态可通过 sho

20、|+-+-+-+-+#其中 File 表示日志文件记录， Position 表示日志文件的位置，这个也是数据库执行复制操作的必须标识，后面两字段表示复制的数据库名和不复制的数据库名，也可以在配置文件中你进行配置。5.2.监控从服务器的状态可以通过：show slave statusG 来查看，另外如果从数据库在复制的过程中出现问题，可以通过命令 reset slave 从数据库服务器复制的线程，从数据库服务器的通常操作命令有：start slave； #启动复制线程stop slave； #停止复制线程reset slave； #重置复制线程change master to； #动态改变到主服

21、务器的配置mysql show slave statusG* 1. row *Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.0.101Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: redhat01-bin.000001Read_Master_Log_Pos: 625Relay_Log_File: redhat02-relay-bin.000002Relay_Log_Pos: 765Relay_Master_Log

22、_File: redhat01-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: mydbReplicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 625Relay_Log_Space: 765Until_C

23、ondition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 01 row in set (0.00 sec)六、手工指定同步mysql CHANGE MASTER TO- MASTER_HOST=master_host_name, /主服务器的 IP 地址- MASTER_USER=repli

24、cation_user_name, /同步数据库的用户- MASTER_PASSWORD=replication_password, /同步数据库的密码- MASTER_LOG_FILE=recorded_log_file_name, /主服务器二进制日志的文件名，前面要求记住的参数- MASTER_LOG_POS=recorded_log_position; /日志文件的开始位置(前面要求记住的参数)备注：不用把主服务器上的初始数据导入从数据库，从数据库会自动的同步你设置同步的数据库的全部内容！七、FAQ7.1. scp 了整个 var 目录到新的从库，start slave 后，发现同步无

25、法正常进行，Slave_IO_Running 正常，但 Slave_SQL_Running 的状态为 No，并且 mysql 错误日志中报找不到一个 releylog 文件答：这是由于 reley-log.info 文件的存在造成的，它记录了数据库当前正在使用的relaylog，而 mysql 默认的 relaylog 命名是以机器名命名的，因此如果从一台机器拷贝到另一台机器，它是找不到这个文件的。如果可以确认 Read_Master_Log_Pos 和Exec_Master_Log_Pos 一致，那么可以直接将 relay-log.info 删除，重启 mysqld，再start slave

26、就可以解决。7.2. start slave 后， Slave_IO_Running 和 Slave_SQL_Running 的状态都为 No，并且日志中报类似“Slave I/O thread: Failed reading log event, reconnecting to retry, log tc-nsop-test00-bin.000177 position 1019586208”的错误答：这是由于主库对从库的同步帐号授权不正确造成的，更改并确认授权正确之后，重新start slave，就可以正常同步。7.3. 如果错误日志中出现如下提示“Warning: You should

27、set server-id to a non-0 value if master_host is set; we force server id to 2, but this MySQL server will not act as a slave.”，并且 Slave_IO_Running 和 Slave_SQL_Running 的状态都为No答：检查主库的 f，这样的错误是由于没有设置主库的 server id 或者 server id 不合法造成的。更改主库 server id 并重启主库后，start slave，同步可以恢复正常。7.4. 如果 Slave_SQL_Running 状

28、态为 No，并且错误日志中有类似“Error Lock wait timeout exceeded; try restarting transaction on query. Default database: ”这样的错误答：这是由于同步某个行的时候恰好遇到有其他 SQL 进程对该行进行了锁定，并且锁定时间较长导致同步进程等待超时。直接 start slave 即可。7.5. 同时启动多台从库的同步进程对主库有什么影响答：通常情况会导致主库的 io 和网卡流量增加。MySQL 的 Binlog Dump 进程是没有限速的，因此会全速进行 binlog 读取和数据分发，给主库带来较大的负担。在实际操作中，尽量依次启动从库，并在启动后观察主库 io，确保 Binlog Dump 进程读取完 binlog 后，再启动下一台。

展开阅读全文