Mysql的主从服务器

    对于公司数据库的需要，我们有时需要建立主从服务器，在这里我给大家演示两个建立过程，一个是对于一个全新的主从服务器的建立（即数据库里还没有存在任何数据信息），另一个是在有数据信息的基础上建立从服务器。

 

在演示之前，先简单介绍一个主从服务器的相关知识：

从服务器是如何实现通过主服务器实现数据复制的：主服务器通过dump线程将主服务器上的二进制日志传递给了从服务器，从服务器将此二进制日志变成自己的中继日志，在通过I/O线程将中继日志传递给本机上的SQL线程，本机通过SQL线程写入数据，来实现数据的复制。 

 

mysql主从服务器的创建方法和需要注意的相关事项

注意事项：

每个服务器都有唯一的service ID

在主服务器上需要建立从服务器复制数据用的账号

若是在互联网上传输需要利用ssl

作为从服务器，其数据库的版本不能比主服务器低，以免一些SQL命令用到的功能不能在从服务器上执行 

 

案例一：全新的没有任何数据

前提：两台主机上都安装好了mysql，其能够正常启动,

 

在主服务器上执行以下操作

[[email protected] ~]# vim /etc/my.cnf  

innodb_file_per_table=1            可添加如下一行使mysql的每个表有自己独立的空间

[[email protected] ~]# service mysqld start  启动mysql

Starting MySQL.....                                        [  OK  ]

 

mysql> grant replication client,replication slave on *.* to [email protected]'172.16.%.%' identified by 'redhat';             在主服务器上添加从服务器可以使用的本地用户，使他对于所有数据库的所有表有权限，其用户名是repl，主机地址是172.16网段的所有地址，密码是redhat

mysql> flush master;   清空主服务器日志

在从服务器上执行以下操作

[[email protected] ~]# vim /etc/my.cnf    修改mysql的配置文件信息

server-id       = 11                不能和主服务器的service ID一样

#log-bin=mysql-bin                 关闭二进制日志，是否关闭可以根据自己的需求决定

relay-log=relay-bin                  开启了中继日志的功能

relay-log-index=relay-bin.index

innodb_file_per_table=1

[[email protected] ~]# service mysqld start 启动mysql

mysql> flush slave;                   清空从服务器日志

mysql>change master to master_host='172.16.19.9',master_user='repl',master_password

='redhat';      指定其主服务器是172.16.19.9上的数据库，复制数据时使用的账号和密码

mysql> start slave;                  开启从服务器

mysql> show slave status\G;          若这两个运行状态都为yes，则主从服务器建立成功

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

可以验证一下是否真正成功

mysql> create database feng;         在主服务器上创建数据库

Query OK, 1 row affected (0.00 sec)

mysql> show databases;              在从服务器上查看

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

| Database           |

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

| information_schema |

| feng               |

| mysql              |

| performance_schema |

| test               |

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

显示出这个表中包含在主服务器上创建的数据库feng，则恭喜你全新的主从服务器创建成功。

 

案例二：给已经有数据的数据库建立从服务器

 

在主服务器上执行以下操作

mysql> grant replication client,replication slave on *.* to [email protected]'172.16.%.%' identified by 'redhat';                         在主服务器上添加用户

mysql> flush logs;

[[email protected] ~]# mysqldump --all-databases --lock-all-tables --master-data=2 >/tmp/all-slave.sql                由于我的也是在虚拟机上新建的mysql， root用户还没有密码，所有执行了这样的命令，

若你的mysql已经有密码，执行的命令则如下

# mysqldump -uroot -p'redhat' --all-databases --lock-all-tables --master-data=2 >/tmp/all-slave.sql                  将数据保存到/tmp/all-slave.sql

      mysqldump是数据库备份命令，-u后跟用户，-p后跟密码 

      --all-databases 指定所有的数据库

      --lock-all-tables 并对所有的表上锁  

      --master-data=2 在备份的时候导出二进制文件的位置

在从服务器上执行以下操作

将在主服务器上备份的文件all-slave.sql，移动到从服务器的/tmp目录下

[[email protected] ~]# vim /etc/my.cnf       修改以下信息

server-id       = 11

#log-bin=mysql-bin

relay-log=relay-bin

relay-log-index=relay-bin.index

innodb_file_per_table=1

[[email protected] ~]# service mysqld start   启动mysql

Starting MySQL..                                           [  OK  ]

[[email protected] ~]# head -30 /tmp/

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=183;

查看文件all-slave.sql，记住如下行从哪个二进制日志开始到哪个点

 

mysql> source /tmp/all-slave.sql;          载入一些备份的数据库

mysql>change master to master_host='172.16.19.9',master_user='repl',master_password

='redhat',master_log_file='mysql-bin.000003',master_log_pos=183;

此处在指定主服务器的时候还要指出从哪个日志的哪个点开始

mysql> start slave;

mysql> show slave status\G;

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

mysql> show databases;

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

| Database           |

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

| information_schema |

| extmail            |

| feng               |

| haha               |

| jiaowu             |

| jun                |

| mydb               |

| mysql              |

| performance_schema |

| test               |

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

10 rows in set (0.00 sec)

通过数据库可以看出备份的数据再入成功

可以验证是否主从服务器真正建立成功

在主服务上执行下面的命令

mysql> show slave hosts;             显示有哪些从服务器

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

| Server_id | Host | Port | Master_id |

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

|        11 |      | 3306 |         1 | 

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

如果你能看到这个表格，同样恭喜你，主从服务器你也已经穿件成功了。

 

 

对于这样创建主从服务器，其实还存在一些隐患的，从服务器可以写入数据的，严格来说从服务器是不允许写入数据的，所以主从服务器可以使用以下配置：

主服务器：
sync_binlog=1                        任何事务提交之后都要写入二进制文件

innodb_flush_logs_at_trx_commit=1     在事务提交的时候将日志立即写入磁盘

这两项来解决服务器突然崩溃造成事务提交但没有写入二进制日志的问题

从服务器：

skip_slave_start=1                     定义mysql启动时不自动进入从服务器线程

read_only=1                          定义从服务没有自己写数据的权限

relay-log=relay-bin                     开启了中继日志的功能

relay-log-index=relay-bin.index           

replicate_do_db=db_name              定义从服务器对哪些数据库进行复制

rpplicate_ignore_db=db_name           定义从服务器不给哪些数据库经行复制

replicate_do_table=tb_name             对表的限制

replicate_ignore_table=tb_name

replicate_wild_do_table=mageedu.tb%    对那些表的限制

replicate_wild_ignore_table

 

若可以对多个数据库复制则要使用一些格式

replicate_do_db=db_name1  

replicate_do_db=db_name2