Mysql主从同步部署

阅读量：6517 次

发布时间：2019-06-24

本文共 8940 字，大约阅读时间需要 29 分钟。

Mysql主从同步部署

概述

1、Mysql主从同从优点

在从服务器可以执行查询工作，降低主服务器压力。

在从主服务器进行备份，避免备份期间影响主服务器服务。

当主服务器出现问题时，可以切换到从服务器。

2、Mysql复制概述

复制过程中一台服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

Mysql使用3个线程来执行复制功能，其中两个线程(Sql线程和IO线程)在从服务器，另外一个线程(IO线程)在主服务器。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以即为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，由从服务器创建，用于读取中继日志并执行日志中包含的更新。在从服务器上，读取和执行更新语句被分成两个独立的任务。当从服务器启动时，其I/O线程可以很快地从主服务器索取所有二进制日志内容，即使SQL线程执行更新的远远滞后。

一、Mysql安装（主从）

1、环境介绍

主服务器：

IP地址：192.168.0.10 主机名称：masterdb01

从服务器：

IP地址：192.168.0.11 主机名称：slavedb02

2、安装依赖包

[root@masterdb01 ~]# yum -y install gcc perl-DBI perl-DBD-MySQL unixODBC ncurses-devel

3、安装Mysql

[root@masterdb01 ~]# tar xvf mysql-5.1.44.tar.gz -C /usr/local/src/

[root@masterdb01 ~]# cd /usr/local/src/mysql-5.1.44/

[root@masterdb01 ~]# useradd mysql -M -s /sbin/nologin

[root@masterdb01 mysql-5.1.44]# vim configure

注释$RM "$cfgfile" 这一行（否则编译时会报错）

[root@masterdb01 mysql-5.1.44]# ./configure --prefix=/usr/local/mysql-5.1.44 --with-unix-socket-path=/usr/local/mysql-5.1.44/data/mysql.sock --localstatedir=/usr/local/mysql-5.1.44/data/ --without-debug --enable-thread-safe-client --enable-assembler --enable-profiling --enable-community-features --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-extra-charsets=all --with-plugins=innobase --with-mysqld-user=mysql --without-embedded-server --with-server-suffix=-community

[root@masterdb01 mysql-5.1.44]# make

[root@masterdb01 mysql-5.1.44]# make install

[root@masterdb01 mysql-5.1.44]# chown -R root.mysql /usr/local/mysql-5.1.44/

[root@masterdb01 mysql-5.1.44]# ./scripts/mysql_install_db --user=mysql

[root@masterdb01 mysql-5.1.44]# cp support-files/my-medium.cnf /etc/my.cnf

[root@masterdb01 mysql-5.1.44]# chown -R mysql.mysql /usr/local/mysql-5.1.44/data/

[root@masterdb01 mysql-5.1.44]# cd /usr/local/mysql-5.1.44/libexec/

[root@masterdb01 libexec]# cp mysqld mysqld.old

[root@masterdb01 libexec]# strip mysqld

[root@masterdb01 libexec]# echo "/usr/local/mysql-5.1.44/lib/mysql/" >> /etc/ld.so.conf

[root@masterdb01 libexec]# ldconfig

[root@masterdb01 libexec]# /usr/local/mysql-5.1.44/bin/mysqld_safe &

[root@masterdb01 libexec]# /usr/local/mysql-5.1.44/bin/mysqladmin -u root password "redhat"

[root@masterdb01 libexec]# export PATH=$PATH:/usr/local/mysql-5.1.44/bin/

[root@masterdb01 libexec]# echo "export PATH=$PATH:/usr/local/mysql-5.1.44/bin/" >> /etc/profile

[root@masterdb01 libexec]# echo "/usr/local/mysql-5.1.44/bin/mysqld_safe &" >> /etc/rc.local

[root@masterdb01 libexec]# /usr/local/mysql-5.1.44/bin/mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.1.44-community-log Source distribution

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

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

+--------------------+

3 rows in set (0.00 sec)

mysql>

到此mysql安装完毕

注：上述配置主从机器都需要配置。

二、Mysql主从配置

1、主服务器配置

1、创建同步用户

mysql> grant replication slave on *.* to 'replication'@'192.168.0.11' identified by 'replication';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Slave上测试: mysql -h 192.168.0.10 -u replication -preplication

2、修改主服务器mysql主配置文件

首先关闭数据库在修改

[mysqld]

socket = /usr/local/mysql-5.1.44/data/mysql.sock

server-id = 1

log-bin=mysql-bin

binlog_format=mixed

Log-slave-updates

log = /var/log/mysql.log

log-error = /var/log/mysql_error.log

long_query_time = 2

log-slow-queries = /var/log/mysql_slow.log

log_queries_not_using_indexes

注释如下三行

#log-bin=mysql-bin

#binlog_format=mixed

#server-id = 1

配置文件详解：

server-id=1 代表主库，2代表从库配置一个唯一的ID编号，编号范围1-32

log-bin=mysql-bin 这个是必须的表示开启二进制日志计入。

bin-do-db=test 需要同步的数据库，如果没有本行表示同步所有的数据库。

binlog-ignore-db=test1 表示不需要同步的数据库名。

log-slave-updates 这个参数一定要加上，否则不会给更新的记录写到二进制文件里（不打开也可以，只是开了以后你可以在从上时时日志恢复）。

expire_logs_days=5 （为避免日志文件过大，设置过期时间为5天超过5天自动删除，一般生产系统设置为0）。

slave-skip-errors 跳过错误，继续执行复制操作。

重启mysql

[root@masterdb01 ~]# mysqladmin shutdown -u root -predhat

[root@masterdb01 ~]# /usr/local/mysql-5.1.44/bin/mysqld_safe &

启动时报错

mysqld_safe mysqld from pid file /usr/local/mysql-5.1.44/data/masterdb01.pid ended

[root@masterdb01 ~]# mysql -u root -p

Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql-5.1.44/data/mysql.sock' (2)

问题解决

原来是mysql用户和权限的问题，重建mysql用户重新修改权限

[root@masterdb01 ~]# useradd mysql -M -s /sbin/nologin

[root@masterdb01 ~]# chown -R root.mysql /usr/local/mysql-5.1.44/

[root@masterdb01 ~]# chown -R mysql.mysql /usr/local/mysql-5.1.44/data/

再从新启动mysql ok问题解决

Mysql启动时可以查看日志有没有报错

[root@masterdb01 ~]# tailf /usr/local/mysql-5.1.44/data/masterdb01.err

3、数据同步

第一次同步之前，确保两边的数据是同步的。

拷贝时首先需要关闭mysql数据库。

2、从服务器配置

1、修改从服务器mysql主配置文件

首先关闭数据库在修改

[root@slavedb02 ~]# vim /etc/my.cnf

socket = /usr/local/mysql-5.1.44/data/mysql.sock

server-id = 2

log-bin=mysql-bin

binlog_format=mixed

log-slave-updates

log = /var/log/mysql.log

log-error = /var/log/mysql_error.log

long_query_time = 2

log-slow-queries = /var/log/mysql_slow.log

log_queries_not_using_indexes

注释如下三行

#log-bin=mysql-bin

#binlog_format=mixed

#server-id = 1

重新启动mysql

[root@slavedb02 ~]# mysqld_safe &

2、指定同步位置（change master）

主服务器查看

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000005 | 106 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

从服务器设置

mysql> change master to master_host='192.168.0.10',master_user='replication',master_password='replication',master_log_file='mysql-bin.000003',master_log_pos=106;

Query OK, 0 rows affected (0.03 sec)

3、启动从服务器线程

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

查看从服务器状态

mysql> show slave status\G;

Slave_IO_Running: No

Slave_SQL_Running: Yes

问题处理

查看原来主从两边的数据不一直导致的

主服务器上 /usr/local/mysql-5.1.44/data下多一个masterdb01.err文件

将其拷贝到从服务器修改属主属组

mysql> slave stop;

mysql> slave start;

mysql> show slave status\G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

ok问题解决

3、验证过程

1、在主服务器创建库和表

mysql> create database test1;

Query OK, 1 row affected (0.03 sec)

mysql> use test1;

Database changed

mysql> create table name (id int(4),name varchar(20));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into name values (1,'test');

Query OK, 1 row affected (0.00 sec)

mysql> select * from name;

+------+------+

| id | name |

+------+------+

| 1 | test |

+------+------+

1 row in set (0.00 sec)

2、在从服务器确认

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

| test1 |

+--------------------+

4 rows in set (0.00 sec)

mysql> use test1;

Database changed

mysql> show tables;

+-----------------+

| Tables_in_test1 |

+-----------------+

| name |

+-----------------+

1 row in set (0.03 sec)

mysql> select * from name;

+------+------+

| id | name |

+------+------+

| 1 | test |

+------+------+

1 row in set (0.00 sec)

mysql>

Ok同步

三、Mysql双向同步配置

从服务器配置

1、在从服务器创建同步帐号

mysql> grant replication slave on *.* to 'replication'@'192.168.0.10' identified by 'replication';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Master上测试: mysql -h 192.168.0.11 -u replication -preplication

2、指定同步位置

从服务器上查看

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000005 | 106 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

主服务器配置

mysql> change master to master_host='192.168.0.11',master_user='replication',master_password='replication',master_log_file='mysql-bin.000005',master_log_pos=106;

Query OK, 0 rows affected (0.03 sec)