mysq的主从复制、备份、还原

在 RH5.4 yum安装 mysql部署master 数据库A，Centos 5.5 编译安装mysql 部署slave数据库B；

mysql 版本信息：

 master  mysql-5.0.77    slave ：mysql-5.0.87        //测试环境并没有考虑版本问题

提示：（关闭selinux）

---------------------------------------------------------------------------------------------

一般情况：mysqladmin -uroot password '123456'设置root用户访问mysql的密码；mysql -uroot -p 键如密码即可登录mysql>界面

一、

##########################在master mysql操作：##########################

1、授权从服务器的root用户来主服务器同步资源的权限

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO root#slave IP     IDENTIFIED BY '123456';             //将#换成@

#如果需要的话添加管理用户，通过mysql的客户端来测试同步的情况

mysql>flush privileges;                                           // 刷新权限，使设置生效

#在node2上使用一下命令测试授权是否成功

#mysql -h 192.168.0.42 -uroot -p

2、在master mysql 配置#vim /etc/my.cnf

# 确保有如下行

server-id=1

log-bin=mysql-bin

binlog-do-db=mytest

binlog-ignore-db=mysql

#binlog-do-db=//需要备份的数据库名，可写多行

#binlog-ignore-db=                             // 不需要备份的数据库名，可写多行

二、##########################在slave mysql操作：###########################

1、编辑/etc/my.cnf

server-id=2

log-bin=mysql-bin

master-host=192.168.0.123

master-user=root

master-password=123456

master-port=3306

replicate-do-db=mytest

可选项如下：

#replicate-do-db=test//需要备份的数据库名

#replicate-ignore-db=mysql//忽略的数据库

# master-connect-retry=60        //如果从服务器发现主服务器断掉，重新连接的时间差(秒)

三、

##########################在master mysql操作：##########################

1、创建测试数据库

mysql> create database mytest;                      //创建mytest库

mysql>showdatabases;

mysql> use mytest;            

mysql> show tables;

+------------------+

|Tables_in_mytest|

+------------------+

|mytable|

+------------------+

1 row in set (0.01 sec)

mysql>ceate table mytable (name VARCHAR(20), sex CHAR(1), birth DATE, birthaddr VARCHAR(20));mysql>describe mytable; 

mysql>insert into mytable values   ('hanfeng','b','1987-08-08','china'); 

mysql>insert into mytable values   ('feiniao','g','1988-08-08','china');

mysql>select * from mytable;

+----------+------+------------+-----------+

|name|sex|birth|birthaddr|

+----------+------+------------+-----------+

|hanfeng|s|1985-08-08|china|

|feiniao|g|1988-08-08|china|

+----------+------+------------+-----------+

 

2、在master使用mysqldump命令将数据库导出：

#mysqldump -uroot mytest  > mysql.sql

3、使用scp命令将mysql.sql拷贝到slave

#scp mysq.sql root#slaveIP:/root                 //将#换成@

四、

##########################在slave mysql操作：###########################

1、创建mytest数据库，

mysql> create database mytest；

4、验证配置的正确性

mysql>start slave；                    // 如果启动失败，参看以下错误处理②

mysql>showslavestatus\G;

***************************1.row*******************

Slave_IO_State:Waitingformastertosendevent必须的

Master_Host:192.168.0.123

Master_User: root

Master_Port: 3306

Connect_Retry:60

Master_Log_File:mysql-bin.000002

Read_Master_Log_Pos:287

Relay_Log_File:mysqld-relay-bin.000009

Relay_Log_Pos:424

Relay_Master_Log_File:mysql-bin.000002

Slave_IO_Running:Yes#为YES状态（说明：如果出现NO请参看下面错误解答！）

Slave_SQL_Running:Yes#为YES状态

Replicate_Do_DB:mytest

Replicate_Ignore_DB:

#####################################################################

                                      此时使用mysqldump的备份与还原基本完成

五、

##########################在mastermysql操作：##########################

mysql>usemytest;

mysql>insert into mytable values  ('shanghai','s','2011-09-13','china');

mysql>select * from mytable;

向mytest数据库的mytable表中插入一新记录；

在slave服务器上

mysql>usemytest;

mysql>select*frommytable;

得到：

mysql>select*frommytable;

+-----------+------+------------+---------------+

|name|sex|birth|birthaddr|

+-----------+------+------------+----------------+

|hanfeng|b|1987-08-08|china|

|feiniao|g|1988-08-08|china|

|shanghai|s|2011-09-13|china|

+-----------+------+------------+---------------+

 3 rows in set (0.01 sec)

 基于mysql数据库文件的主从同步与AB复制及数据备份到此完成！

 

总结：

##############################  错误处理 ① ##########################

对于       Slave_IO_Running: No

Slave_SQL_Running:Yes

错误解答

Master slave 复制错误

Description：

Slave_IO_Running:NO

Slave_SQL_Running:Yes

Seconds_Behind_Master:NULL

本人遇到的Slave_IO_Running:NO的情况有下面两种：

1.在配置slave同步时因为slave访问master没有权限导致；

2.master上的mysql-bin.xxxxxx文件全被我误删除了；

对于第一种情况，仔细检查数据库访问权限即可解决；

对于第二种情况：

mysql>showslavestatus\G

***************************1.row***************************

Slave_IO_State:

Master_Host:192.168.0.123

Master_User:slave

Master_Port:3306

Connect_Retry:60

Master_Log_File:mysql-bin.000016

Read_Master_Log_Pos:173

Relay_Log_File:mysqld-relay-bin.000008

Relay_Log_Pos:98

Relay_Master_Log_File:mysql-bin.000016

Slave_IO_Running:No

Slave_SQL_Running:Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

     Replicate_Ignore_Table: br>          

                  Last_Error: 0

Skip_Counter:0

Exec_Master_Log_Pos:173

Relay_Log_Space:98

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

1 row in set (0.00 sec)

解决步骤：

重启master库：servicemysqldrestart

mysql>showmasterstatus;

+------------------+--------------------+----------------+------------------+

|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|

+------------------+-------------------+-----------------+------------------+

|mysql-bin.000001|98|||

+------------------+--------------------+-----------------+------------------+

mysql>slavestop;

mysql>changemastertoMaster_Log_File='mysql-bin.000001',Master_Log_Pos=98;

mysql>slavestart;

mysql>showslavestatus\G

***************************1.row***************************

Slave_IO_State:Waitingformastertosendevent

Master_Host:192.168.0.123

Master_User:slave

Master_Port:3306

Connect_Retry:60

Master_Log_File:mysql-bin.000001

Read_Master_Log_Pos:98

Relay_Log_File:mysqld-relay-bin.000002

Relay_Log_Pos:235

Relay_Master_Log_File:mysql-bin.000001

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

Replicate_Do_DB:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

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:98

Relay_Log_Space:235

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

1rowinset(0.00sec)

问题：如果你的mysql数据库已经有数据存储使用，将导致master与slave日志错误！

mysql>startslave;

ERROR1201(HY000):Couldnotinitializemasterinfostructure;moreerrormessagescanbefoundintheMySQLerrorlog

mysql>showslavestatus;

Empty set (0.00 sec)

处理：删除mysql-bin.000001 日志文件，并重启mysql服务

1、master

#rm-rf/var/lib/mysql/*.*

2、slave

#rm-rf/usr/local/mysql/var/*.*

3、servicemysqldrestart