MySql-主从复制

~]# wget https://downloads.mariadb.org/interstitial/mariadb-10.1.11/source/mariadb-10.1.11.tar.gz [root@localhost ~]# yum -y install gcc gcc-c++ make cmake ncurses-devel ncurses libxml2 libxml2-devel openssl-devel bison bison-devel #安装编译环境 [root@localhost ~]# tar xf mariadb-10.1.11.tar.gz -C /usr/local/ [root@localhost ~]# cd /usr/local/ [root@localhost local]# ln -s mariadb-10.1.11/ mysql [root@localhost local]# cd mysql [root@localhost mysql]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mydata -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STPRAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWIYH_READLINE=1 -DWIYH_SSL=system -DVITH_ZLIB=system -DWITH_LOBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci [root@localhost mysql]# make && make install

剩余步骤与mysql编译安装方法相同，此处不再详写

配置过程：
版本：
1、双方的MySQL版本要一致；
2、如果不一致：主的要低于从的；

主服务器：
1、改server-id
2、启用二进制日志
3、创建有复制权限的帐号

从服务器：
1、改server-id
2、启用中继日志
3、连接主服务器
4、启动复制线程

复制开始的位置：
1、都从0开始：
2、若主服务器已经运行一段时间，并且存在不小的数据集，此时需要先将主服务器数据库进行备份，然后在从服务恢复，从主服务器上复制时从主服务器所处的位置开始复制；

（1）从0开始复制
配置主服务器：

安装好后配置文件中默认已经启用二进制日志，主节点server-id可以先不用修改，但是两个节点的server-id不能相同，二进制日志文件不要和数据文件放在一块，然后开始创建有复制权限的帐号：

MariaDB [(none)]> grant replication slave,replication client on *.* to ‘repluser‘@‘192.168.%.%‘ identified by ‘replpass‘;      
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

配置从服务器：

[root@localhost ~]# vim /etc/my.cnf     #只需修改以下几项

#log-bin=mysql-bin
#关闭二进制日志
server-id       = 11
#修改server-id
relay-log       = /mydata/relaylogs/relay-bin
#启用中继日志

[root@localhost ~]# mkdir /mydata/relaylogs   #创建目录

[root@localhost ~]# chown -R mysql.mysql /mydata/


MariaDB [(none)]> show global variables like ‘%relay%‘;
+-----------------------+-----------------------------------+
| Variable_name         | Value                             |
+-----------------------+-----------------------------------+
| max_relay_log_size    | 1073741824                        |
| relay_log             | /mydata/relaylogs/relay-bin       |
| relay_log_basename    | /mydata/relaylogs/relay-bin       |
| relay_log_index       | /mydata/relaylogs/relay-bin.index |
| relay_log_info_file   | relay-log.info                    |
| relay_log_purge       | ON                                |
| relay_log_recovery    | OFF                               |
| relay_log_space_limit | 0                                 |
| sync_relay_log        | 10000                             |
| sync_relay_log_info   | 10000                             |
+-----------------------+-----------------------------------+
10 rows in set (0.05 sec)

然后启动复制功能：

MariaDB [(none)]> change master to master_host=‘192.168.1.106‘,master_user=‘repluser‘,master_password=‘replpass‘;  
Query OK, 0 rows affected (0.05 sec)

MariaDB [(none)]> start slave;  #启动slave
Query OK, 0 rows affected (0.03 sec)

[root@localhost data]# tail -f  localhost.localdomain.err  #查看日志文件
2016-01-31  2:01:13 140230526023648 [Note] InnoDB: Waiting for purge to start
2016-01-31  2:01:13 140230526023648 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.26-76.0 started; log sequence number 1616829
2016-01-31  2:01:13 140230526023648 [Note] Plugin ‘FEEDBACK‘ is disabled.
2016-01-31  2:01:13 140229866223360 [Note] InnoDB: Dumping buffer pool(s) not yet started
2016-01-31  2:01:13 140230526023648 [Note] Server socket created on IP: ‘::‘.
2016-01-31  2:01:13 140230526023648 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘10.1.11-MariaDB‘  socket: ‘/tmp/mysql.sock‘  port: 3306  Source distribution
2016-01-31  2:10:05 140230215559936 [Note] ‘CHANGE MASTER TO executed‘. Previous state master_host=‘‘, master_port=‘3306‘, master_log_file=‘‘, master_log_pos=‘4‘. New state master_host=‘192.168.1.106‘, master_port=‘3306‘, master_log_file=‘‘, master_log_pos=‘4‘.
2016-01-31  2:14:53 140230214953728 [Note] Slave SQL thread initialized, starting replication in log ‘FIRST‘ at position 0, relay log ‘/mydata/relaylogs/relay-bin.000001‘ position: 4
2016-01-31  2:14:54 140230215256832 [Note] Slave I/O thread: connected to master ‘repluser@192.168.1.106:3306‘,replication started in log ‘FIRST‘ at position 4

在这里主要是看:
Slave_IO_Running=Yes
Slave_SQL_Running=Yes

在主服务器插入数据：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      641 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show  databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      762 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

查看从服务器，已经收到数据了：

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

MariaDB [(none)]> stop slave;    #停止从服务器服务
Query OK, 0 rows affected (0.01 sec) 

[root@localhost ~]# service mysqld stop  #停止服务器重新启动后，slave会自动启动
Shutting down MySQL.. SUCCESS! 
[root@localhost ~]# service mysqld start
Starting MySQL. SUCCESS! 
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.11-MariaDB Source distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> show warnings;
+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Note  | 1254 | Slave is already running |
+-------+------+--------------------------+
1 row in set (0.00 sec)

（2）从半道复制
主服务器：

MariaDB [(none)]> drop database mydb;       #首先在主从节点先删除数据库
Query OK, 0 rows affected (0.01 sec)

[root@cacti mydata]# mysql < hellodb.sql     #导入自定义的数据库
[root@cacti mydata]# mysqldump --all-databases --flush-logs --master-data=2 --lock-all-tables > all.sql  #先备份
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

[root@cacti mydata]# scp all.sql  root@192.168.1.113:~  #将数据复制到从服务器

MariaDB [(none)]> show  master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 |      366 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 

从服务器：

[root@localhost ~]# mysql < all.sql 

 MariaDB [(none)]> stop slave;   #确保slave功能关闭
 MariaDB [(none)]> change master to master_host=‘192.168.1.106‘,master_user=‘repluser‘,master_password=‘replpass‘,master_log_file=‘mysql-bin.000002‘,master_log_pos=366;  
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.03 sec)

在主服务器端插入数据进行MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| hellodb |
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+——————–+
6 rows in set (0.00 sec)器：

MariaDB [(none)]> show  databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

可以看到两边数据一致，说明复制已经没有问题了。

从半道复制和从0复制其实差别不大，只是在从服务器中连接主服务器时需要指定二进制日志文件及其位置即可。

三、更多知识

1、主从服务器时间要同步（ntp）：
  */5 * * * * /usr/sbin/ntpdate 192.168.1.1

2、如何限制从服务器只读？
  read-only=ON （在/etc/my.cnf 中定义）
  注意：仅能限制那不具有SUPER权限用户无法执行写操作；
  想限制所有用户：
  mysql> FLUSH TABLES WITH READ LOCK;

3、如何主从复制时的事务安全？
  在主服务器上配置：
  sync_binlog=1 #每次提交立即将缓冲去内容同步到日志,需要关闭自动提交功能

4、复制过滤器：为了节约资源并提高服务器的性能，可以设置过滤器只复制希望备份的数据库，在配置的文件 my.cnf 中的 [mysqld] 块中使用以下配置进行过滤：
master上把事件从二进制日志中过滤:
    binlog_do_db= #复制哪些数据库，白名单
    binlog_ignore_db= #相反的，黑名单

slave上事件从中继日志中过滤:
    replicate_do_db= #数据库的白名单
    replicate_ignore_db= #数据库的黑名单
    replicate_do_table= db_name.table_name #表的白名单
    replicate_ignore_table= #表的黑名单
    replicate_wild_do_table= #支持通配符的白名单
    replicate_wild_ignore_table= #支持通配符的黑名单
MySQL 5.5以上，下面的这些表都建议过滤掉，只复制生产环境数据。
    replicate-wild-ignore-table =mysql.%
    replicate-wild-ignore-table =test.%
    replicate-wild-ignore-table =log.%
    replicate-wild-ignore-table =information_schema.%
    replicate-wild-ignore-table =performance_schema.%

四、配置过程的问题
1）设定 Replication 完成后, start slave后出现 “Could not initialize master info structure”

1.MariaDB [(none)]> reset slave; # 重点就是这行
2.MariaDB [(none)]> CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=107; # 请依照自行环境设定
3.MariaDB [(none)]> start slave; # 就正常了.

重新设置 slave, MASTER_LOG_FILE 和 MASTER_LOG_POS 会被清空, 所以需要重新设置.

2）mysql 主从同步失败 Last_IO_Error: Got fatal error 1236 from master

先进入slave中执行:”slave stop;”来停止从库同步；
再去master中执行:”flush logs;”来清空日志；
然后在master中执行:”show master status;”查看下主库的状态，主要是日志的文件和position；
然后回到slave中，执行:”CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.000004′,MASTER_LOG_POS=106;”，文件和位置对应master中的;
最后在slave中执行:”slave start;”来启动同步。

MySql-主从复制

标签：