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使用。
3)、查询主数据库状态,并记下FILE及Position的值,这个在后面配置从服务器的时候要用到。
在从服务器上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 &
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;
4)、启动slave同步。
mysql> start slave;
5)、检查主从同步,如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes,则主从复制连接正常。Slave_IO_Running: Yes(网络连接状态)Slave_SQL_Running: Yes(表结构正常)
mysql> show slave status\G
验证配置是否正常,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');
在从数据库中验证一下,是否正常复制到数据。
[root@localhost ~]# mysql -uroot –p123456
mysql> show databases;
mysql> select * from mysqltest.user;
从上图中的结果,我们可以看到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](/e/u/themes/default/p_w_picpaths/spacer.gif)
3)、查询主数据库状态,并记下FILE及Position的值,这个在后面配置从服务器的时候要用到。
mysql> show master status;
![spacer.gif](/e/u/themes/default/p_w_picpaths/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](/e/u/themes/default/p_w_picpaths/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](/e/u/themes/default/p_w_picpaths/spacer.gif)
4)、启动slave同步。
mysql> start slave;
![spacer.gif](/e/u/themes/default/p_w_picpaths/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](/e/u/themes/default/p_w_picpaths/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](/e/u/themes/default/p_w_picpaths/spacer.gif)
在从数据库中验证一下,是否正常复制到数据。
[root@localhost ~]# mysql -uroot –p123456
mysql> show databases;
![spacer.gif](/e/u/themes/default/p_w_picpaths/spacer.gif)
mysql> select * from mysqltest.user;
![spacer.gif](/e/u/themes/default/p_w_picpaths/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后,再启动下一台。