mysql的复制过程:每执行一个写操作,它都会往自己的数据库中存一份,与此同时这个写操作也会存储在二进制日志文件中一份,并且把它们保存为事件,所以在这个数据库上,前端数据每执行一个写操作或者有可能引起修改的操作,都会保存一个事件,我们就把这个事件通过mysql服务器3306端口发送给另外一台服务器,另外一台服务器把这个事件接收下来,接受下来以后先保存在本地的日志文件中,而后从这个日志文件中一次读一个事件并且在本地执行一下,然后保存在数据库里面,这个过程就叫mysql的复制。
安装mysql的过程就不讲了,直接开始我们的主从复制的配置过程:
1、 开启master和slave的二进制日志功能,也就是在mysql的主配置文件/usr/local/mysql/etc/my.cnf中,添加log_bin=mysql-bin,将master的server_id 设置为1,slave的server_id=2.
下面是master的配置文件
[root@localhost ~]# cat /usr/local/mysql/etc/my.cnf | grep -v ^#| grep -v ^$
[mysqld]
server_id = 1
log_bin=mysql-bin
log-bin-index = mysql-bin.index
log-error = /var/log/mysql/mysql-error.log
general_log = 1
general_log_file = /var/log/mysql/mysql.log
user = mysql
basedir = /usr/local/mysql
datadir = /datadir
port = 3306
socket = /var/lib/mysql/mysql5.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
接下来是slave的配置文件
[root@wordpress ~]# cat /usr/local/mysql/etc/my.cnf | grep -
v ^# | grep -v ^$
[mysqld]
server_id = 2
log_bin=mysql-bin
log-bin-index = mysql-bin.index
general_log = 1
general_log_file = /var/log/mysql/mysql.log
log-error=/var/log/mysql/mysql.error
basedir = /usr/local/mysql
datadir = /database
port = 3306
socket = /var/run/mysqld/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
2、在主服务器上,设置一个从数据库的账户,使用REPLICATION
SLAVE赋予权限
mysql> grant replication slave on *.* to 'tt'@'192.168.254.153' identified by '123456';
Query OK, 0 rows affected (0.06 sec)
mysql> show master status \G; 查看当前master的二进制日志状态和起始点
*************************** 1. row ***************************
File: mysql-bin.000011
Position: 330
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.01 sec)
mysql> flush tables with read lock; 将mysql的数据库锁表,仅仅允许读,以保证数据的一致性
Query OK, 0 rows affected (0.06 sec)
[root@localhost ~]# mysqldump -uroot -p123 blog > blog.sql 将blog这个库以脚本的形式导出来,便于导入slave
mysql> unlock tables; 解锁
Query OK, 0 rows affected (0.00 sec)
3、 slave上的操作
[root@wordpress ~]# mysql -u root -p123456 < blog.sql
mysql> change master to master_host='192.168.2
54.46',master_user='master',master_password='m
aster',master_log_file='mysql-bin.000011',master_log_pos=330;
Query OK, 0 rows affected, 2 warnings (0.13 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.254.46
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 330
Relay_Log_File: wordpress-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
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: 330
Relay_Log_Space: 120
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /database/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
4、测试
在master上新建表,
mysql> use blog;
Database changed
mysql> create table hi_tb(id int(3),name char(10));
Query OK, 0 rows affected (0.14 sec)
mysql> show tables;
+----------------+
| Tables_in_blog |
+----------------+
| hi_tb |
+----------------+
1 row in set (0.00 sec)
在slave查看
mysql> use blog;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql slave没有发现新建的表,说明主从复制中间出了问题,所以需要查看mysql错误日志
tail -f /var/log/mysql/mysql.error
2013-09-22 15:39:21 2150 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended.
Please see the MySQL Manual for more about this issue and possible alternatives.
2013-09-22 15:39:21 2150 [Note] Slave I/O thread: connected to master 'master@192.168.254.46:3306',replication started in log 'mysql-bin.000011' at position 330
2013-09-22 15:39:21 2150 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2013-09-22 15:39:21 2150 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000011' at position 330, relay log './wordpress-relay-bin.000001' position: 4
2013-09-22 15:39:21 2150 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'master'@'192.168.254.153' (using password: YES) (Errno: 1045), Error_code: 1597
2013-09-22 15:39:21 2150 [ERROR] Slave I/O thread couldn't register on master
2013-09-22 15:39:21 2150 [Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log 'mysql-bin.000011' at position 330, Error_code: 1597
2013-09-22 15:39:21 2150 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2013-09-22 15:39:21 2150 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'master'@'192.168.254.153' (using password: YES) (Errno: 1045), Error_code: 1597
2013-09-22 15:39:21 2150 [ERROR] Slave I/O thread couldn't register on master
错误原因,没有在master上给master用户授权!!
mysql> select * from hi_tb; (master)
+------+------+
| id | name |
+------+------+
| 1 | bobu |
| 2 | dsfa |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from hi_tb; (slave)
+------+------+
| id | name |
+------+------+
| 1 | bobu |
| 2 | dsfa |
+------+------+
2 rows in set (0.01 sec)
实现了同步。
最后,再次对比一下主从的二进制日志
分享到:
相关推荐
MySQL编译安装、主从复制的配置(CentOS-6.6+MySQL-5.6)
mysql5.6.14配置主从复制 ,自己搭建的mysql5.6.14配置主从复制步骤记录
mysql5.6-5.5主从备份 A服务器(主服务器Master):9.1.6.217 mysql:5.6 redhat5.8 B服务器(从服务器Slave):localhost mysql:5.5 win10
mysql5.6安装主从配置,包含详细的主从配置,mysql数据库的编码格式
MySQL5.6主从复制(读写分离)教程 1、MySQL5.6开始主从复制有两种方式: 基于日志(binlog); 基于GTID(全局事务标示符)。 需要注意的是:GTID方式不支持临时表!所以如果你的业务系统要用到临时表的话就不要考虑...
MySQL5.6主从复制搭建文档
本文详细描述了MySQL 5.6 主从复制功能的详细搭建步骤及相关参数说明,保证一次成功。文末附带主从切换方法。
Red Hat6.4上mysql5.6主从复制配置文档 MySQL数据库服务器Master和slave,Master为主服务器,slave为从服务器.实现主从备份。
mysql主从热备
讲解linux系统下mysql主从复制的方法。
主要介绍了MySQL5.6主从复制也就是mysql数据同步配置方法,需要的朋友可以参考下
MHA实现mysql5.6主从切换安装配置教程.docx
仅供学习使用,希望能对大家有所帮助,
主要介绍了mysql5.6 主从复制同步详细配置,但不是很详细推荐大家看下脚本之家以前的文章,需要的朋友可以参考下
mysql5.6安装+主从复制安装,方便你快速上手,及学习。
mysql5.6的主从搭建和主从切换-详细笔记文档总结