mysql主从参数_MySQL主从配置

MySQL主从(MySQL Replication)，主要用于MySQL的时时备份或者读写分离。在配置之前先做一下准备工作，配置两台 mysql 服务器，如果你的机器不能同时跑两台 Linux 虚拟机，那可以考虑在同一个机器上跑两个 mysql 服务。

一、原理

1、每个从仅可以设置一个主。

2、主在执行 sql 之后，记录二进制 log 文件(bin-log)。

3、从连接主，并从主获取 binlog，存于本地 relay-log，并从上次记住的位置起执行 sql，一旦遇到错误则停止同步。

二、推论

1、主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间，主从数据不一致。

2、如果主从的网络断开，从会在网络正常后，批量同步。

3、如果对从进行修改数据，那么很可能从在执行主的 bin-log 时出现错误而停止同步，这个是很危险的操作。所以一般情况下，非常小心的修改从上的数据。

4、一个衍生的配置是双主，互为主从配置，只要双方的修改不冲突，可以工作良好。

5、如果需要多主的话，可以用环形配置，这样任意一个节点的修改都可以同步到所有节点。

6、可以应用在读写分离的场景中，用以降低单台 MySQL 服务器的 I/O。

7、可以实现 MySQL 服务的 HA 集群。

8、可以是 1 主多从，也可以是相互主从(主主)。

三、主从配置

说明：主库IP：192.168.0.109从库IP：192.168.0.104

1、安装MySQL

分别在主库的服务器和从库的服务器上安装mysql，版本都为MySQL-5.1.73；详细步骤见我之前的文章LAMP环境搭建中mysql的安装配置，在这里就不阐述了。

2、主库的配置

首先创建一个数据库db1

[root@tpp ~]# mysql

mysql> create database db1;

mysql> quit

注：数据库安装完成后默认会生成两个库：mysql库与test库；为了做方便试验，我们直接把mysql库复制给db1库(下面采用备份恢复方法来实现)，你也可以自己在db1库里新建表。[root@tpp ~]# mysqldump mysql > 123.sql

[root@tpp ~]# mysql db1 < 123.sql

[root@tpp ~]# mysql

mysql> use db1;

mysql> show tables;

如图：

接着退出，编辑配置文件[root@tpp ~]# vim /etc/my.cnf          //在[mysqld]段添加以下内容

server-id = 1

log-bin=mysql-bin

max_binlog_size = 500M

binlog_cache_size = 128K

binlog-do-db = db1

log-slave-updates

expire_logs_day=2

binlog_format=mixed

如图：

参数说明：(1)server-id = 1                           //服务器标志号,一组主从中此标识号不能重复。

(2)log-bin=mysql-bin                       //开启bin-log，并指定文件目录和文件名前缀。

(3)max_binlog_size=500M                    //当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。

(4)binlog_cache_size = 128K          //日志缓存大小

(5)binlog-do-db = db1                //需要同步的数据库名字，如果是多个，以逗号隔开继续加库名

注：如果有很多库要设置主从，则可以设黑名单。

binlog-do-db = db1

改为：

binlog-ignore-db = mysql              //不需要同步的数据库名字。

(6)log-slave-updates                 //当Slave从Master数据库读取日志时更新新写入日志中，如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。

(7)expire_logs_day=2                 //bin-log日志文件保存的天数

binlog_format=mixed                 //bin-log日志文件格式为：MIXED，可以防止主键重复

注：配置expire_logs_day与binlog_format参数，只有mysql版本是5.0以上才有。

然后重启mysql服务[root@tpp ~]# /etc/init.d/mysqld restart

Shutting down MySQL....           [确定]

Starting MySQL..                  [确定]

在/data/mysql/目录(即MySQL安装目录)下可以看到生成了以mysql-bin开头的一些文件，如下图：

最后我们添加一个用于主从同步的用户：repl,密码为：123456[root@tpp ~]# mysql

mysql> grant replication slave on *.* to 'repl'@'192.168.0.109' identified by '123456';

mysql> flush privileges;               //刷新权限

mysql> flush tables with read lock;    //锁死表读

mysql> show master status;             //查看状态，待会儿要用这个参数

如图：

3、从库的配置

首先新建一个和主数据库一样的db1库。我同样是复制之前的123.sql数据库到bd1库，这个是通过主服务器中的vsftpd文件服务下载到本地，然后恢复到db1库。[root@tpp ~]# lftp test2@192.168.0.109

口令:

lftp test2@192.168.0.109:~> ls

-rw-r--r--    1 504      504             0 Sep 14 17:36 1.txt

-rw-r--r--    1 0        0          527174 Sep 22 21:12 123.sql

drwxr-xr-x    2 504      504          4096 Sep 14 17:36 222

下载在本地

lftp test2@192.168.0.109:/> get 123.sql -o /data/

lftp test2@192.168.0.109:/> quit

复制123.sql库到db1库[root@tpp ~]# cd /data/

[root@tpp data]# mysql -e "create database db1"

[root@tpp data]# mysql db1 < 123.sql

[root@tpp data]# mysql

mysql> use db1;

mysql> show tables;    //可以看到表和主一样

接着编辑配置文件

[root@tpp ~]# vim /etc/my.cnf        //修改以下地方

server-id = 2

master-host=192.168.0.109

master-user=repl

master-password=123456

master-port=3306

master-connect-retry=30             //控制重试间隔，默认为60秒

slave-skip-errors=1062              //忽略错误，1062为主键重复错误

replicate-do-db = db1

slave-skip-errors=1007,1008,1053,1062,1213,1158,1159 //忽略一些其他错误，不影响数据库完整性

保存退出。重启下mysql服务[root@tpp ~]# /etc/init.d/mysqld restart

Shutting down MySQL....           [确定]

Starting MySQL..                  [确定]

同步数据库

[root@tpp ~]# mysql

mysql> slave stop;

mysql> change master to master_host='192.168.0.109',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000021',master_log_pos=524861;

mysql> slave start;mysql> show slave status\G;

显示出现了错误：从数据库无法同步Slave_IO_Running: No

Slave_SQL_Running: Yes

Seconds_Behind_Master: NULLLast_IO_Error:error connecting to master 'repl@192.168.0.109:3306' - retry-time: 60 retries: 86400

这个问题很有可能就是密码不对，而我的问题是因为没有关闭防火墙，关闭防火墙后我再show下，发现问题解决了。

4、测试主从是否同步

首先在主数据库上解锁，查看表内容mysql> unlock tables;

mysql> use db1

mysql> show tables;

然后查看下从数据库是否生成了db1数据库，这里可以看到表的内容和主数据库中db1库是一样的。

测试下：

在主数据库上执行下面的命令删除event表mysql> drop table event;

接着在从数据库上可以发现该表也被同步的删除了

这样就实现了主从数据同步。

实现轻松部署 MySQL 主从复制：参考 mysqldump 的 --master-data 参数。

mysqldump导出数据时，当这个参数的值为1的时候，mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。默认情况下这个值是1；当这个值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。

3步即可：

1、主上导出数据库：mysqldump 带--master-data=1参数

2、从上导入数据库：mysql 导入数据命令

3、start slave；