mysql之主从复制


使用mysql主从复制的好处有:


1、采用主从服务器这种架构,稳定性得以提升。如果主服务器发生故障,我们可以使用从服务器来提供服务。


2、在主从服务器上分开处理用户的请求,可以提升数据处理效率。


3、将主服务器上的数据复制到从服务器上,保护数据免受意外的损失。


环境描述:


新企业要搭建架构为主从复制的mysql数据库。


主服务器(mysql-master):IP地址:192.168.1.10,mysql已安装,没有用户数据。


从服务器(mysql-slave):IP地址:192.168.1.11,mysql已安装,没有用户数据。


主从服务器均可正常提供服务。






主从复制配置如下:


在主服务器上192.168.1.10操作:


1)、确保/etc/my.cnf中有如下参数,没有的话需手工添加,并重启mysql服务。


[mysqld]


log-bin=mysql-bin 启动二进制文件


server-id=1 服务器ID


2)、登录mysql,在mysql中添加一个backup的账号,并授权给从服务器。


[root@localhost ~]# mysql -uroot –p123456 登录mysql


mysql> grant replication slave on *.* to 'backup'@'192.168.1.11' identified by 'backup'; 创建backup用户,并授权给192.168.1.11使用。


spacer.gif



3)、查询主数据库状态,并记下FILE及Position的值,这个在后面配置从服务器的时候要用到。


mysql> show master status;

spacer.gif


在从服务器上192.168.1.11操作:


1)、确保/etc/my.cnf中有log-bin=mysql-bin和server-id=1参数,并把server-id=1修改为server-id=10。修改之后如下所示:


[mysqld]


log-bin=mysql-bin 启动二进制文件


server-id=10 服务器ID


另附从库的模板:


[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-locking

key_buffer = 384M

max_allowed_packet = 1M

table_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

skip-name-resolve

master-connect-retry=30

wait_timeout = 1728000

interactive-timeout = 1728000

expire_logs_days = 7

slave-skip-errors=1062




2)、重启mysql服务。


[root@localhost ~]# mysqladmin -p123456 shutdown

[root@localhost ~]# mysqld_safe --user=mysql &


spacer.gif


3)、登录mysql,执行如下语句


[root@localhost ~]# mysql -uroot –p123456


mysql> change master to master_host='192.168.1.10',master_user='backup',master_password='backup',master_log_file='mysql-bin.000003',master_log_pos=401;


spacer.gif


4)、启动slave同步。


mysql> start slave;


spacer.gif


5)、检查主从同步,如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes,则主从复制连接正常。Slave_IO_Running: Yes(网络连接状态)Slave_SQL_Running: Yes(表结构正常)


mysql> show slave status\G


spacer.gif




验证配置是否正常,mysql主从能否正常复制。


在主数据库上新建一个库,并且在库中写一个表和一些数据。


[root@localhost ~]# mysql -uroot –p123456


mysql> create database mysqltest;


mysql> use mysqltest;


mysql> create table user(id int(5),name char(10));


mysql> insert into user values (00001,'zhangsan');


spacer.gif



在从数据库中验证一下,是否正常复制到数据。


[root@localhost ~]# mysql -uroot –p123456


mysql> show databases;


spacer.gif



mysql> select * from mysqltest.user;


spacer.gif



从上图中的结果,我们可以看到mysql主从复制已经在起作用了,我们在主数据库中写入的数据已经复制到我们的从数据库中了。
四 Error:
1.Slave_SQL_Running: NO(第一种解决方法)
     mysql>stop slave;
     mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
     mysql>start slave;
2.Slave_SQL_Running: NO(表结构不正常)(第二种解决方法)
Failed to open the relay log './mysqld-relay-bin.000001' (relay_log_pos 4)
报如上错误,就是Slave里面的二进制表没有和Master的二进制表同步。
解决方法:
登入Master数据库:
mysql>show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 |      866 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
记住file(master-bin.000003)和Position(866)
mysql> flush tables with read lock;(先锁住主库表)
[root@mysql1 ~]# mysqldump -u root -p --all-database > mysql.sql(导出数据库文件)
[root@mysql1 ~]#scp mysql.sql 192.168.1.222:/root/(把数据库文件传到Slave数据库)
[root@mysql2 mysql]# mysql  -u root -p --all-database < mysql.sql(导入数据库文件)
登入Slave数据库:
mysql> stop slave;(停止Slave的复制功能)
mysql> reset slave;(重置slave)
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.1.221',master_user='lsh',master_password='123456',master_log_file='master-bin.000003',master_log_pos=866;
Query OK, 0 rows affected (0.05 sec)
mysql>start slave;(开启Slave的复制功能)
mysql>show slave status\G(查看状态,IO和SQL都为YES)
登入Master数据库
mysql>unlock tables;(解除表锁定)
mysql>create database etongbao;
登入Master数据库
mysql>show databases;
查看复制情况,如果有表示复制成功。
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,就可以正常同步。
3  如果错误日志中出现如下提示“Warning: You should 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
答:检查主库的my.cnf,这样的错误是由于没有设置主库的server id或者server id不合法造成的。更改主库server id并重启主库后,start slave,同步可以恢复正常。
4 如果Slave_SQL_Running状态为No,并且错误日志中有类似“Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: ……”这样的错误
答:这是由于同步某个行的时候恰好遇到有其他SQL进程对该行进行了锁定,并且锁定时间较长导致同步进程等待超时。直接start slave即可。
5  同时启动多台从库的同步进程对主库有什么影响
答:通常情况会导致主库的io和网卡流量增加。MySQL的Binlog Dump进程是没有限速的,因此会全速进行binlog读取和数据分发,给主库带来较大的负担。在实际操作中,尽量依次启动从库,并在启动后观察主库io,确保Binlog Dump进程读取完binlog后,再启动下一台。