5.myqsl主从配置

5.myqsl主从配置

5.1 主从同步的定义

    主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服

务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置

文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

使用主从同步的好处：

• 1.通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。

• 2.提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据。

• 3.在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。

5.2 主从同步的机制

    Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件

来保持和主服务器的数据一致。在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可

以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句。每一个从服务器会记录关于二进制日志的信

息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服

务器的连接。主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项），另外，每一个从

服务器还需要通过CHANGEMASTERTO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）

5.3 配置主从同步的基本步骤

有很多种配置主从同步的方法，可以总结为如下的步骤：

• （1）在主服务器上，必须开启二进制日志机制和配置一个独立的ID
• （2）在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号
• （3）在开始复制进程前，在主服务器上记录二进制文件的位置信息
• （4）如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）
• （5）配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置

5.4 详细配置主从同步的方法

5.4.1数据备份

如果在设置主从同步前，主服务器上已有大量数据，可以使用 mysqldump 进行数据备份并还原到从服务器以实现数据的复制。执行命令：

$ mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql

--all-databases ：导出所有数据库
--lock-all-tables ：执行操作时锁住所有表，防止操作时有数据修改
~/master_db.sql :导出的备份数据（sql 文件）位置，可自己指定

• 数据还原命令:

$ mysql –uroot –pmysql < master_db.sql

5.4.2配置主服务器master

• 编辑设置mysqld的配置文件，设置log_bin和server-id

server-id不能相同，主服务器配置为1，从服务器配置其他数值

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# 启用log_bin和server-id两项配置

• 重启服务：

  $ sudo service mysql restart

  • 登入主服务器Ubuntu中的mysql，创建用于从服务器同步数据使用的帐号

$ mysql –uroot –pmysql
mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';

mysql> show master status;

mysql> flush privileges;

mysql> show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec)

File为使用的日志文件名字，Position为使用的文件位置，这两个参数必须记下，配置从服务器时会用到。

5.4.3配置从服务器器slave

• 编辑设置mysqld的配置文件，server-id

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# 修改server-id不能与mysql主服务相同，2开始的数值

$ mysql –uroot –pmysql
mysql> change master to master_host='192.168.1.10',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=107;

注：master_host：主服务器 Ubuntu 的 ip 地址
master_log_file: 前面查询到的主服务器日志文件名
master_log_pos: 前面查询到的主服务器日志文件位置

• 开启同步，查看同步状态

mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.10
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: yangwanyan-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000001
             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: 107
              Relay_Log_Space: 414
              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)

• 新建数据库表和用户

mysql>  create database ttsx charset='utf8';
mysql> grant all on ttsx.* to 'test-database'@'%' identified by '123456';