MySQL主从服务器配置.doc

MySQL 主从服务器配置一、同步原理说明Mysql的 Replication 是一个异步的复制过程，从一个 Mysql instace(我们称之为 Master)复制到另一个Mysql instance(我们称之 Slave)。在 Master 与 Slave之间的实现整个复制过程主要由三个线程来完成，其中两个线程(Sql线程和IO线程)在 Slave 端，另外一个线程(IO线程)在 Master端。要实现 MySQL 的 Replication ，首先必须打开 Master 端的Binary Log(mysql-bin.xxxxxx)功能，否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使用“log-bin” 参数选项，或者在 f 配置文件中的 mysqld 参数组(mysqld标识后的参数部分)增加“log-bin” 参数项。MySQL 复制的基本过程如下：1. Slave 上面的IO线程连接上 Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;2. Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 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. Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。这样，实际上就是在 Master 端和 Slave端执行了同样的 Query，所以两端的数据是完全一样的。二、设置mysql主从配置的优点：1.解决web应用系统，数据库出现的性能瓶颈，采用数据库集群的方式来实现查询负载；一个系统中数据库的查询操作比更新操作要多得多，通过多台查询服务器将数据库的查询分担到不同的查询服务器上从而提高查询效率。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-db=mysql #不记录数据库mysql的更新日志binlog-do-db = #记录指定的数据库的更新日志，还可以单独处理只更新表内容此外，需要注意两个环境变量：innodb_flush_log_at_trx_commit=1 #Default: 1。在使用了事务的数据库中，强烈建议该值显式的设置为1，以确保在每个事务提交时，日志缓冲被写到日志文件，并将该写操作同步到磁盘上sync_binlog=1 #在每进行1次binlog写操作后就将该操作同步到硬盘上。设为更高的值可以有效降低io并获得更好的性能，但会增加bilog丢失的风险为从服务器上的同步帐号授权：mysqlGRANT 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端口，默认为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 slave;$mysqladmin -uroot -p shutdown确认mysqld停止之后，将其中的数据文件ib*，需要同步的数据库目录和其他相关文件拷贝到新机器上。binlog和relay-log不用拷贝3.3.2 方式2：在主服务器或其中一台从服务器上设置锁，禁止一切读写操作并拷贝数据在主服务器上执行:mysqlFLUSH TABLES WITH READ LOCK;导出数据到从数据库(可以使用tar、scp、mysqldump等方式，此处实现比较灵活):$cd /mysql/$tar zcvf var.tar.gz var/ #数据目录可能不为var目录然后是拷贝此var.tar.gz包到从服务器并解压缩到相应目录在主服务器上执行:mysqlUNLOCK TABLES;说明： 拷贝主服务器的数据到从服务器的实现方式太多：可以直接停止mysqld程序拷贝数据、可以直接tar方式打包处理、可以直接scp方式处理、可以直接导出相应表结构等，具体实现方式看具体应用，适而择之，主要保证在拷贝数据的时候没有数据写入和更改即可3.4 启动同步启动从数据库的mysqld,推荐使用-skip-slave-start启动，进入mysql后再start slave启动同步。说明： 通常使用的mysqld_safe会记住启动时的参数，例如-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,语法如下：master_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 MASTER_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-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 #指定同步用户的用户名为replicationmaster-password = rep #指定同步用户的密码为PassWORdreplicate-do-db = iknow #指定需要同步的库，每个库独占一行replicate-do-db = iknow_admreplicate-do-db = .log-slave-updates #在从库上对同步进行日志记录，磁盘占用量较大，建议从库运行稳定后将其去除，如要作为第二层主数据库，则此项必须这里的设置可以在后面登陆进从数据库之后用CHANGE MASTER TO来修改。4.3.拷贝数据:从一台从数据库C拷贝数据到B 从数据库没有对外提供服务，因此可以直接停掉从数据库拷贝数据。注意：停从数据库的时候需要显式的执行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*，，以及需要同步的库cp到本地目录var.$DATE下（主要是为了缩短C上mysqld的服务停止时间）$ cd $MYSQL/var; mkdir ./var.$DATE$ cp -r ib* iknow iknow_adm ./var.$DATE/$ scp $MYSQL/var.$DATE B:/mysql4.4.在主数据库上为一台从库授权同步mysqlGRANT REPLICATION SLAVE ON *.* TO replicationB机器的ip地址 IDENTIFIED BY Password;4.5.在B上启动mysqld$ mysqld_safe -skip-slave-start &4.6.进入mysql观察slave statusmysql show slave status4.7.观察和$ cat $MYSQL/$ cat $MYSQL/在这里不需要change master一步是因为中已记录了master_log_file和master_log_pos等相关信息。这是一种比较保险而且简单的办法4.8.复查确认f中replication slave配置正确如果文件存在的话，mysql是优先读取它的，但在某些情况下，比如mysqld异常重启等，有可能会读取f中配置，因此需要保证f中的replication slave配置正确 由于mysqld在正常情况下优先读取，因此，在主数据库上一定要保证是不存在的，否则可能出现不可预知的后果。4.9. CHANGE MASTER TO语句这是用于在mysql命令行中显式的更改主库的语句。在mysql命令行中执行mysql help change master to可获得关于这条语句的详细信息。如果在进行主从数据库切换后，或需要临时更改主库，而又不想重启数据库的时候，可以使用如下语句动态的更改主库：mysql CHANGE MASTER TO MASTER_HOST=xxx.xxx.xxx.xxx, MASTER_USER=replication, MASTER_PASSWORD=Password, MASTER_LOG_FILE=master-binlog.00001, MASTER_LOG_POS=123456;以上语句各项含义如下：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.监控主服务器的状态可通过show master status来监控主服务器的状态，内容如下：mysql show master status;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| redhat01-bin.000001 | 625 | mydb | | +-+-+-+-+1 row in set (0.00 sec)+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000003 | 1164 | | |+-+-+-+-+#其中File表示日志文件记录，Position表示日志文件的位置，这个也是数据库执行复制操作的必须标识，后面两字段表示复制的数据库名和不复制的数据库名，也可以在配置文件中你进行配置。5.2.监控从服务器的状态可以通过：show slave statusG来查看，另外如果从数据库在复制的过程中出现问题，可以通过命令reset slave从数据库服务器复制的线程，从数据库服务器的通常操作命令有：start slave；#启动复制线程stop slave；#停止复制线程reset slave；#重置复制线程change master to； #动态改变到主服务器的配置mysql show slave statusG* 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 01 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: redhat01-bin.000001 Read_Master_Log_Pos: 625 Relay_Log_File: redhat02-relay-bin.000002 Relay_Log_Pos: 765 Relay_Master_Log_File: redhat01-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 625 Relay_Log_Space: 765 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: 01 row in set (0.00 sec)六、手工指定同步mysql CHANGE MASTER TO- MASTER_HOST=master_host_name, /主服务器的IP地址- MASTER_USER=replication_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后，发现同步无法正常进行，Slave_IO_Running正常，但Slave_SQL_Running的状态为No，并且mysql错误日志中报找不到一个releylog文件答：这是由于文件的存在造成的，它记录了数据库当前正在使用的relaylog，而mysql默认的relaylog命名是以机器名命名的，因此如果从一台机器拷贝到另一台机器，它是找不到这个文件的。如果可以确认Read_Master_Log_Pos和Exec_Master_Log_Pos一致，那么可以直接将删除，重启mysqld，再start slave就可以解决。7.2. start slave后， Slave_IO_