Mysql的主主备份与主从备份--同步

一：实验目标

mysql主从原理

实战：mysql主从配置

实战：mysql主主配置

 

二：实验环境

导入测试数据库book：

Mysql主xuegod63   IP：192.168.1.63

Mysql从xuegod64   IP：192.168.1.64

 

三：实验代码

实战：mysql主从配置 

服务概述：主从复制

    复制解决的基本问题是让一台服务器的数据和另外的服务器保持同步。 一台主服务器可以连接多台从服务器，并且从服务器也可以反过来作主服务器。主服务器和从服务器可以位亍丌同的网络拓扑中，还能对整台服务器、特定的数据库，甚至特定的表迚行复制。 主从服务器的版本必须一致，即使丌一致，主服务器版本可以是旧的，从服务器必须是新的版本。

 

复制解决的问题

MySQL复制技术有以下一些特点：

(1) 数据分布 (Data distribution )

(2) 负载平衡(load balancing)

(3) 备份(Backups)

(4) 高可用性和故障转移 High availability and failover

 

复制如何工作

整体上来说，复制有3个步骤：

(1) master将改发记录到二迚制日志(binary log)中（这些记录叨做二迚制日志事件，binary log events）；

(2) slave将master的binary log events拷贝到它的中继日志(relay log)；

(3) slave重做中继日志中的事件，修改salve上的数据

 

服务概述：

 

I/O  表示进和出，input和output

一）：运行过程

1：master输入一条规则，binary log会将其转换成二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
2：slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
3：SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
    

此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

 

二）：复制配置 

有两台MySQL数据库服务器Master和slave，Master为主服务器，slave为从服务器，初始状态时，Master和slave中的数据信息相同，当Master中的数据发生变化时，slave也跟着发生相应的变化，使得master和slave的数据信息同步，达到备份的目的。

三）：要点：

负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输，从服务器的各种修改动作。因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。

    一台主服务器可以连接多台从服务器，并且从服务器也可以反过来作主服务器。 

主服务器和从服务器可以位于不同的网络拓扑中，还能对整台服务器、特定的数据库，甚至特定的表迚行复制。 

主从服务器的版本必须一致，即使不一致，主服务器版本可以是旧的，从服务器必须是新的版本。 

 

3.1 . 复制解决的问题，MySQL复制技术有以下一些特点： 

(1) 数据分布 (Data distribution )

(2) 负载平衡(load balancing)

(3) 备份(Backups)

(4) 高可用性和故障转移 High availability and failover

 

3.2 复制如何工作

整体上来说，复制有3个步骤：

(1) master将改发记录到二迚制日志(binary log)中（这些记录叨做二迚制日志事件，binary log events）；

(2) slave将master的binary log events拷贝到它的中继日志(relay log)；

(3) slave重做中继日志中的事件，修改salve上的数据。

 

模式：C/S 模式

端口： 3306

四）：实验步骤---实战mysql主从备份-同步

xuegod63 主mysql服务器配置 ---创建要同步的数据库： 

创建数据库

mysql> create database mk;

 

mysql> use mk;

mysql> create table test1 (id int);

mysql> insert into test1 values(1)

 

 

停止mysql服务 

[root@xuegod63 ~]# service mysql stop

 

编辑配置文件 

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

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0 #在原配置文件中，添加以下内容： 

log-bin=mysqllog #启用二迚制日志，默认存在/var/lib/mysql 下面

server-id=1 #本机数据库ID 标示。

binlog-do-db=mk #可以被从服务器复制的库。二进制需要同步的数据库名

# binlog-ignore-db=mk2 不可以被从服务器复制的库

 

重新启劢 mysql服务器

[root@xuegod63 ~]# service mysqld restart

 

授权给用户名为“slave”用户登录密码及权限

mysql> grant replication slave on *.* to slave@192.168.1.64 identified by "123456";

查看状态，信息

 

 

数据库备份

[root@xuegod63 ~]# mysqldump -u root -p123456 --all-databases > all.sql

[root@xuegod63 ~]# ll -h all.sql

-rw-r--r-- 1 root root 514K Mar 27 00:54 all.sql

 

把主服务器中的数据传给从服务器：复制前保证两个数据库数据一致： 

1):方法1：scp all.sql 192.168.1.64:/root

 

 

方法2：使用nc命令 

   NetCat，它短小精悍、功能实用，被设计为一个简单、可靠的网络工具，可通过TCP或UDP协议传输读写数据。同时，它还是一个网络应用Debug分析器，因为它可以根据需要创建各种不同类型的网络连接。

语法：

服务器端：nc収送数据的语法： nc -l 端口 < 要传输的文件

客户端： nc接叐数据的语法： nc 进程nc服务器端IP 端口 > 文件名

 

例：监听9999端口，当有客户端连接时，就把对应文件传送到来连接的客户端 

[root@xuegod63 ~]# rpm -qf `which nc `

nc-1.84-22.el6.x86_64

开启监听：

[root@xuegod63 ~]#nc -l 9999 < all.sql

[root@xuegod63 ~]# netstat -antup | grep 9999

tcp 0 0 0.0.0.0:9999 0.0.0.0:* LISTEN 15164/nc

 

测试接收： 

1）配置从服务器xuegod64 ： 

[root@xuegod64 ~]# cd /opt/

[root@xuegod64 opt]# ls

[root@xuegod64 opt]# nc 192.168.1.63 9999 > ncall.sql

[root@xuegod64 opt]# diff ncall.sql /root/all.sql

============================================================

 

2):数据库版本相同

Mysql主服务器xuegod63

 

mysql从服务xuegod64

[root@xuegod64 opt]# yum install mysql-server -y  

[root@xuegod64 opt]# service mysqld restart

mysql>show variables like '%version%';查看版本

 

 

测试连接到主服务器是否成功

[root@xuegod64 opt]# mysql -u slave -h 192.168.1.63 -p123456

 

 

从服务器导入数据库和主服务器保持一致

[root@xuegod64 ~]# mysql -u root -p < all.sql

Enter password:123456

[root@xuegod64 opt]# mysql -u root -p

Enter password:123456

 

 

/////////////////////////////////////////////////////////////////////////注释：在这里可能会报错

 

解决方法

方法操作流程很简单，如下：

# /etc/init.d/mysqld stop //停止MySQL服务的运行
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking & //跳过受权表访问
# mysql -u root mysql //登录mysql

 

1：关闭mysqld服务

[root@xuegod64 ~]# service mysqld stop

2：跳过授权访问

[root@xuegod64 ~]#  mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

3：修改数据库更新

[root@xuegod64 ~]# mysql -u root mysql#在mysql5.7以下的版本如下：
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root' and host='127.0.0.1' or host='localhost';//把空的用户密码都修改成非空的密码就行了。
mysql> FLUSH PRIVILEGES;
mysql> quit

4：重启数据库并重设密码

[root@xuegod64 ~]# service mysqld start  :
[root@xuegod64 ~]# mysql -uroot -p
Enter password: <输入新设的密码newpassword>

/////////////////////////////////////////////////////////////////////////

 

修改从服务器配置文件：从服务器没必要开bin-log日志注。

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

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

#在配置文件中写入以下内容

server-id=2 #从服务器ID号，不要和主ID相同 ，如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器以及其它从服务器的不相同。可以认为server-id值类似亍IP地址：这些ID值能唯一识别复制服务器群集中的每个服务器实例。

master-host=192.168.1.63 #指定主服务器IP地址

master-user=slave #指定在主服务器上可以进行同步的用户名

master-password=123456 #指定在主服务器上可以进行同步的用户名的密码

####以下可以不写

master-port = 3306 #同步所用的端口

master-connect-retry=60 #断点重新连接时间

 

重启mysql服务 

[root@xuegod64 ~]# service mysqld restart

 

测试： 主服务器上查看：

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

Enter password:  

mysql> show master status;

 

#证明主服务器成功

 

从服务器上查看：--#证明从服务器成功

[root@xuegod64 opt]# mysql -u root -p

Enter password:  

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.63

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqllog.000001

Read_Master_Log_Pos: 315

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 459

Relay_Master_Log_File: mysqllog.000001

Slave_IO_Running: Yes #可以看到这两个Yes，说明从服务器安装成功。

Slave_SQL_Running: Yes

Slave_IO_Running ：一个负责不主机的io通信

Slave_SQL_Running：负责自己的slave mysql迚程

 

测试：数据同步

xuegod63写数据： 

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

Enter password:

mysql> use mk;

mysql> show tables;

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

| Tables_in_mk |

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

| test1         |

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

1 row in set (0.00 sec)

mysql> insert into test1 values(5);

 

xuegod64读数据：

[root@xuegod64 opt]# mysql -u root -p

Enter password:

mysql> use mk;

 

排错：

同步之前如果怀疑主从数据不同步可以采取上面冷备份进程拷贝法或者在从服务器上命行同步方法。

 

 

 

实战：mysql主主配置 

mysql主：服务端：xuegod63.cn IP：192.168.1.63

mysql主：服务端：xuegod64.cn IP：192.168.1.64

 

配置xuegod64 ： 身份1： xuegod63的主。

                  身份2： xuegod63的从。

root@xuegod64 ~]# vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

server-id=2

master-host=192.168.1.63

master-user=slave

master-password=123456

####to master

log-bin=mysqlslave-bin-log

binlog-do-db=mk

binlog-ignore-db=mysql #避免同步mysql用户 相关配置。

 

授权名为slave64用户登录密码及权限：

[root@xuegod64 opt]# mysql -u root -p123456

mysql> grant replication slave on *.* to slave64@'192.168.1.63' identified by '123456';

[root@xuegod64 opt]# service mysqld restart

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

Enter password:

 

#说明xuegod64 作为mysql 主已经成功。

 

配置xuegod63 ： 身份1： xuegod64的主。

                  身份2： xuegod64的从。

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

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

### to master -64

symbolic-links=0

log-bin=mysqllog

server-id=1

binlog-do-db=mk

 

### to slave -64

master-host=192.168.1.64

master-user=slave64

master-password=123456

replicate-do-db=mk

[root@xuegod63 ~]# service mysqld restart

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

Enter password:123456

mysql> show slave status \G;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 4

Current database: mk

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.64 #主服务器是64

Master_User: slave64  #授权的用户是slave64

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqlslave-bin-log.000001

Read_Master_Log_Pos: 106

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 260

Relay_Master_Log_File: mysqlslave-bin-log.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: mk

Replicate_Ignore_DB:

 

测试主主数据同步：

xuegod64主服务器 添加表

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

Enter password:123456

mysql> use mk;

mysql> create table test2(id int);

mysql> create table xuegod (id int);

mysql> insert into test2 values(10);

mysql> insert into xuegod values(100);

 

xuegod63从服务器-测试

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

Enter password:123456

mysql> use mk;