Windows与Linux上MySql数据库主从同步

原理

众所周知，主从同步有两种数据库类型：master、slave。

master会将自己所经历的一切动作都记录在binlog(二进制文件中)，而slave拿到这个文件就会产生自己的relay log(中继日志)，slave再根据中继日志进行执行。

话说完成一次同步将会用到三个线程：binlog dump线程、i/o线程、sql线程；其中binlog dump线程为master所有，i/o线程、sql线程则为slave所有。

首先主库创建binlog dump线程，目的是观察并记录master的动作，接着master将信息异步传达给i/o线程；之后slave则需要产生i/o线程来与主库进行连接，目的是获取binlog信息；最后i/o线程将根据binlog文件创造出中继日志，slave的sql线程根据中继日志执行更新产生与主库相同的数据。而relay log在用过一次后就被会被清除，这样可以减少对磁盘的占有。

master与slave之间对应关系

它们之间有几种对应关系呢？

单master——单slave

单master——单master

单master——多并slave

多串master——多并slave

这些对应关系会应用到具体的工作环境中。

mysql数据库版本

这里准备在Windows和Linux虚拟机各自的mysql数据库之间建立主从同步关系。Windows上mysql为主库，Linux上mysql为从库，使其之间能异步的传递数据。

需要的两个mysql数据库准备好了，版本信息如下：

windows：mysql-5.5.48-winx64.zip

Linux：MySQL-server-5.5.48-1.linux2.6.x86_64.rpm、MySQL-client-5.5.48-1.linux2.6.x86_64.rpm

二者之间版本需要尽量接近，最好一致。

确保主从彼此连接

windows与linux的数据库需要建立连接所以需要彼此ping通，首先关闭各自的防火墙

linux关闭防火墙命令：

service iptabes stop

接着查看各自的ip地址

windows——ipconfig

linux——ifconfig

linux ping ipv4地址:

ping 192.168.43.61

windows ping inet地址:

ping 192.168.159.31

各自的配置文件信息

windows——my.ini

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:Program Files (x86)mysql-5.5.48-winx64
# 设置mysql数据库的数据的存放目录
datadir=C:Program Files (x86)mysql-5.5.48-winx64data
#======================主从同步需要的属性配置=====================
#主数据库的唯一id
server-id=1
#行为日志，自命名，可辨即可。当你配置好这个属性的时候，就会触发binlog功能
log-bin=C:Program Files (x86)mysql-5.5.48-winx64datamysql_log_bin
#错误日志，自命名，可辨即可
log-err=C:Program Files (x86)mysql-5.5.48-winx64datamysql_log_err
#允许被记录binlog的数据库列表
#binlog-do-db=
#不允许被记录binlog的数据库列表
#binlog-ignore-db=
#读写控制：0代表可读可写，1代表只读。
read-only=0
#========================================================
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

注：蓝色为开启主从同步的必要条件，其它有关主从配置的属性不配也可以。

server-id，无论master还是slave，server-id只能有一个，且不能相同。

log-bin，会保存binlog二进制文件，只要它被配置进去就会开始记录。

需要注意的是mysql_log_bin和log-err这两个文件夹的位置必须直接在data文件夹下，否则mysql服务无法启动。

如果写成这样：log-err=C:Program Files (x86)mysql-5.5.48-winx64datalogmysql_log_err 服务器就无发正常启动。

binlog-do-db、binlog-ignore-db两个属性不进行设置的话，master默认会对未来所有的数据库进行记录binlog。不过在主从同步机制开启之前的旧数据并不会被slave同步，因为binlog没有其对应记录。所以说，如果之前master里已经有数据，而又想被slave同步可以有几种解决方法。其中一个方法比较繁琐：是先屏蔽写锁，然后对旧数据进行备份>>再删除旧数据>>最后打开被屏蔽的写锁、恢复旧数据，恢复旧数据的动作就会被记录，并同步到slave中；另一个方法比较简单：是通过mysqldump导出master数据，然后复制全部的备份的文件到从数据库即可。

read-only有两种状态：0代表可读可写，1代表只读。

配置并保存，接着cmd以管理员身份重启windows上mysql服务（或者直接在计算机管理>服务——找到mysq服务启动就可以了）。

下面用命令控制mysql服务重启：

net stop mysql

net start mysql

linux——my.cnf

由于我的/etc下没有my.cnf文件所以需要去/usr/share/mysql/my-medium.cnf复制一份到/etc，取名为my.cnf即可，二者不会冲突。

# Example MySQL config file for medium systems.

# This is for a system with little memory (32M - 64M) where MySQL plays

# an important part, or systems up to 128M where MySQL is used together with

# other programs (such as a web server)

# MySQL programs look for option files in a set of

# locations which depend on the deployment platform.

# You can copy this option file to one of those

# locations. For information about these locations, see:

# http://dev.mysql.com/doc/mysql/en/option-files.html

# In this file, you can use all long options that a program supports.

# If you want to know which options a program supports, run the program

# with the "--help" option.

# The following options will be passed to all MySQL clients

[client]

#password = your_password

port = 3306

socket = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port = 3306

socket = /var/lib/mysql/mysql.sock

skip-external-locking

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

# Don't listen on a TCP/IP port at all. This can be a security enhancement,

# if all processes that need to connect to mysqld run on the same host.

# All interaction with mysqld must be made via Unix sockets or named pipes.

# Note that using this option without enabling named pipes on Windows

# (via the "enable-named-pipe" option) will render mysqld useless!

#skip-networking

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin

# binary logging format - mixed recommended

binlog_format=mixed

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

#server-id = 1

# Replication Slave (comment out master section to use this)

# To configure this host as a replication slave, you can choose between

# two methods :

# 1) Use the CHANGE MASTER TO command (fully described in our manual) -

# the syntax is:

# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,

# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;

# where you replace <host>, <user>, <password> by quoted strings and

# <port> by the master's port number (3306 by default).

# Example:

# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,

# MASTER_USER='joe', MASTER_PASSWORD='secret';

# OR

# 2) Set the variables below. However, in case you choose this method, then

# start replication for the first time (even unsuccessfully, for example

# if you mistyped the password in master-password and the slave fails to

# connect), the slave will create a master.info file, and any later

# change in this file to the variables' values below will be ignored and

# overridden by the content of the master.info file, unless you shutdown

# the slave server, delete master.info and restart the slaver server.

# For that reason, you may want to leave the lines below untouched

# (commented) and instead use CHANGE MASTER TO (see above)

# required unique id between 2 and 2^32 - 1

# (and different from the master)

# defaults to 2 if master-host is set

# but will not function as a slave if omitted

server-id = 2

# The replication master for this slave - required

#master-host = <hostname>

# The username the slave will use for authentication when connecting

# to the master - required

#master-user = <username>

# The password the slave will authenticate with when connecting to

# the master - required

#master-password = <password>

# The port the master is listening on.

# optional - defaults to 3306

#master-port = <port>

# binary logging - not required for slaves, but recommended

#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /var/lib/mysql

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = /var/lib/mysql

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

#innodb_buffer_pool_size = 16M

#innodb_additional_mem_pool_size = 2M

# Set .._log_file_size to 25 % of buffer pool size

#innodb_log_file_size = 5M

#innodb_log_buffer_size = 8M

#innodb_flush_log_at_trx_commit = 1

#innodb_lock_wait_timeout = 50

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[myisamchk]

key_buffer_size = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

注：蓝色为需要改动部分，该文件默认开启了binlog功能。其它属性就无需改动了。

保存更改，接着需要重启linux上mysql服务：

service mysql restart

windows上master授权给slave

首先windows上cmd登陆mysql：

mysql -hlocalhost -uroot -p

接着键入授权命令：

用户授权（给自身授权，必须执行，否则slave的io线程可能连接不上，并处于"Connecting"状态）

grant all privileges on *.* to 'root'@'%' identified by '' with grant option;

给slave用户授权，使它拥有权力复制

grant replication slave on *.* to 'root'@'192.168.159.31' identified by ' ' ;

'root'是数据库用户名。

‘192.168.159.31’是ens33里的inet。

‘ ’是数据库密码（我没有设置数据库密码）；

之后命令刷新一下：

flush privileges;

查看当前master的状态

show master status

可以看到4个字段

File就是已经生成的binlog，在my.ini配置文件中可以自定义binlog文件名，所以我的文件名是mysql_log_bin.000003，因此说我们有对这个binlog文件的命名权力。

可以看到data文件夹中生成的文件：

position是slave当前读取文件的位置，称为”偏移量“会一直发生变动，即slave会从这个点进行数据同步。

binlog_do_db、binlog_ignore_db由于没有进行配置所以里面不会有数据库名称列表。

如果你没有在my.ini中配置主从同步信息的话，在你键入“show master status“后会显示：“Empty set”。

在linux的mysql中指定master信息

首先在控制台登陆到linux上的mysql

mysql -uroot -p

[root@localhost etc]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14
Server version: 5.5.48-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

接着为slave指定master信息

change master to master_host='192.168.43.61',

master_user='root',

master_password='',

master_log_file='mysql_log_bin.000003' ,

master_log_pos=331;

mysql> change master to master_host='192.168.43.61',master_user='root',master_password='',
-> master_log_file='mysql_log_bin.000003',master_log_pos=331;

同时给mysql的root用户授权(一定要授权，否则，io线程可能连接不上)

grant all privileges on *.* to 'root'@'%' identified by '' with grant option;

mysql> grant all privileges on *.* to 'root'@'%' identified by '' with grant option;
Query OK, 0 rows affected (0.05 sec)

我的mysql数据库也是没有设置密码，请针对自身密码进行设置。

启动slave

start slave；

mysql> start slave;
Query OK, 0 rows affected (0.11 sec)

显示slave的状态

show slave status;（稍有点乱）

mysql> show slave status;
+----------------------+---------------+-------------+-------------+---------------+----------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------------+---------------+-------------+-------------+---------------+----------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+
| Connecting to master | 192.168.43.61 | root | 3306 | 60 | mysql_log_bin.000003 | 331 | localhost-relay-bin.000001 | 4 | mysql_log_bin.000003 | Connecting | Yes | | | | | | | 0 | | 0 | 331 | 107 | None | | 0 | No | | | | | | NULL | No | 1130 | error connecting to master 'root@192.168.43.61:3306' - retry-time: 60 retries: 86400 | 0 | | | 0 |
+----------------------+---------------+-------------+-------------+---------------+----------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.06 sec)

mysql>

或者

show slave statusG;

mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.43.61
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_log_bin.000003
Read_Master_Log_Pos: 331
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql_log_bin.000003
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
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: 331
Relay_Log_Space: 107
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: 1130
Last_IO_Error: error connecting to master 'root@192.168.43.61:3306' - retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

可以看到橙色部分的io线程状态不为Yes，而是connecting，并且有错误的日志生成，所以处于异常状态。因为master的Exec_Master_Log_Pos已经不再是331了(中途我操作了master数据库)，导致pos改变。

这是其中一点原因，还有一点是我之前没有对各自的数据库用户赋予所有权限：grant all privileges on *.* to 'root'@'%' identified by '' with grant option;

所以说：Slave_IO_Running 和 Slave_SQL_Running都必须为Yes才是正确的状态，否则就是有问题。

引起不为Yes的原因主要有：

1 两个数据库的权限不足

2 指定的master或slave的个别参数错误，如：用户名或密码不正确、master_log_pos参数不匹配

3 网络有问题

所以在排除以上因素后我按照原有顺序重新执行了一遍：

windows中：

可以看到position已经变化为1756，因此需要重新在slave中对master进行指定参数。

linux中：

mysql> change master to master_host='192.168.43.61',master_user='root',master_password='', master_log_file='mysql_log_bin.000003',master_log_pos=1756;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_host='192.168.43.61',master_user='root',master_password='', master_log_file='mysql_log_bin.000003',master_log_pos=1756;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

接着

show slave statusG;

mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.43.61
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_log_bin.000003
Read_Master_Log_Pos: 1756
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 257
Relay_Master_Log_File: mysql_log_bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 1756
Relay_Log_Space: 417
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
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: 1
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

这就是正确的方式，至此主从同步就实现了。

测试

可以用navacat来测试一下，这里是windows上navicat，现在在master上新建立一个数据库”db0325“，使用该数据库，并在db0325中新建一个表”test“，之后向test表插入信息并查询。

注：以下数据同上图

mysql> create database db0325;(数据库命名不能有下划线_）      --新建数据库
Query OK, 1 row affected

mysql> use db0325; --使用当前数据库
Database changed

mysql> create table test(                                --新建表
stuname varchar(20) primary key,
stusex char(3),
stuage int(10),
birthday datetime not null
);
Query OK, 0 rows affected

mysql> insert into test (stuname,stusex,stuage,birthday) values ('张三','男',36,('1985-2-25,00:00:01'));   --插入语句
Query OK, 1 row affected

mysql> select * from test;                                 --查询
+---------+--------+--------+---------------------+
| stuname | stusex | stuage | birthday |
+---------+--------+--------+---------------------+
| 张三 | 男 | 36 | 1985-02-25 00:00:01 |
+---------+--------+--------+---------------------+
1 row in set

mysql>

来到linux

看slave是否和刚才的信息同步。

mysql -uroot -p

show databases;

可以看到除了自带的数据库以外，多了一个db0325。

use db0325;

select * from test;

测试没有出现问题，主从同步成功。

从结果可以验证；slave的确只能从得到binlog的时候同步数据，而之前的数据就要通过mysqldump备份的方式导入到slave中。

好啦，看到这里如果你也想配置MySQL数据库间的主从同步，就赶快动手配置一下吧，如果您有更加新奇的知识点，欢迎补充，如有不足之处还请赐教>>>

Windows与Linux上MySql数据库主从同步

相关推荐