mysql主从事务复制_MySQL主从复制

一、主从服务器设置。

主服务器Ip：172.16.22.6

从服务器ip：172.16.22.8

二、主从服务器都需要设置的

主从服务器下载安装mariadb-5.5.36：

1、解压

tar xf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local

2、创建软链接

cd /usr/local

ln -sv mariadb-5.5.36-linux-x86_64 mysql

3、创建MySQL数据存放位置

mkdir /mydata/data -pv

mkdir /mydata/binlogs -pv

mkdir /mydata/reaylogs -pv (从服务器上创建)

4、创建mysql组，mysql用户

groupadd -r mysql

useradd -r -g mysql -s /sbin/nologin mysql

5、修改目录的属主、属组

chown -R mysql.mysql /mydata/*

6、修改mariadb安装目录的属主、属组

cd /usr/local/mysql

chown -R root.mysql ./

7、复制mariadb的配置文件和启动文件

mkdir /etc/mysql

cd support-files

cp my-large.cnf /etc/mysql/my.cnf

cp mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

chkconfig --add mysqld

8、导入二进制文件

vim /etc/profile.d/mysqld.sh

export PATH=/usr/local/mysql/bin:$PATH

主服务器配置：vim /etc/mysql/my.cnf

添加：

datadir = /mydata/data

修改：

log-bin=/mydata/binlogs/master-bin

从服务器配置vim /etc/mysql/my.cnf

添加：

datadir = /mydata/data

relay-log = /mydata/reaylogs/relay-log

修改：

#log-bin=mysql-bin

#binlog_format=mixed

server-id = 11

9、初始化MySQL数据库

scripts/mysql_install_db --datadir=/mydata/data --user=mysql

三、主服务器需要另外设置的：

创建有复制权限的从服务器账号：MariaDB [(none)]> grant replication slave, replication client on *.* to repluser@'172.16.%.%' identified by 'replp@ss';

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

或者：

MariaDB [(none)]> grant replication slave, replication client on *.* to repluser@'172.16.22.8' identified by 'replp@ss';

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

查看主节点的二进制日志处在那个文件的那个位置：MariaDB [(none)]> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| master-bin.000001 | 495 | | |

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

1 row in set (0.00 sec)

四、从服务器需要另外设置的

使用有复制权限的账号连接master

1、连接主服务器时如果报：

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.22.6',MASTER_USER='repluser',MASTER_PASSWORD='replp@ss',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=495;

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log

说明master.info文件出错。

解决方法：

MariaDB [(none)]> reset slave;

Query OK, 0 rows affected (0.00 sec)

作用：删除/mydata/data/master.info文件MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.22.6',MASTER_USER='repluser',MASTER_PASSWORD='replp@ss',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=495;

Query OK, 0 rows affected (0.03 sec)

查看从节点状态：MariaDB [(none)]> show slave status\G

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

Slave_IO_State:

Master_Host: 172.16.22.6

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 495

Relay_Log_File: relay-log.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: master-bin.000001

Slave_IO_Running: No

Slave_SQL_Running: No

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: 495

Relay_Log_Space: 245

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: NULL

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: 0

1 row in set (0.01 sec)

2、启动IO_THREAD、SQL_THREAD线程：MariaDB [(none)]> START SLAVE IO_THREAD;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> START SLAVE SQL_THREAD;

Query OK, 0 rows affected (0.00 sec)

3、再次查看从节点状态：MariaDB [(none)]> SHOW SLAVE STATUS\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.22.6

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 495

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 530

Relay_Master_Log_File: master-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: 495

Relay_Log_Space: 818

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)

主从复制完成。

五、验证一下：

在主节点上创建数据库testdb

MariaDB [(none)]> create database testdb;

Query OK, 1 row affected (0.00 sec)

在从节点上看testdb，是否创建成功。MariaDB [(none)]> show databasts;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'databasts' at line 1

MariaDB [(none)]> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| testdb |

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

5 rows in set (0.00 sec)

查看从节点状态：MariaDB [(none)]> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.22.6

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 582

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 617

Relay_Master_Log_File: master-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: 582

Relay_Log_Space: 905

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)

附：

1、停止从节点复制功能方法：MariaDB [(none)]> stop slave;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G

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

Slave_IO_State:

Master_Host: 172.16.22.6

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000003

Read_Master_Log_Pos: 582

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 617

Relay_Master_Log_File: master-bin.000003

Slave_IO_Running: No

Slave_SQL_Running: No

2、启动从节点复制功能方法：MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G

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

Slave_IO_State:

Master_Host: 172.16.22.6

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000003

Read_Master_Log_Pos: 582

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 617

Relay_Master_Log_File: master-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3、复制中应该注意的问题：

1、如何限制从服务器只读？

更改slave的全局服务器变量read-only为YES；

注意：此限制对于拥有SUPER权限用户无效；

MariaDB [mysql]> SET GLOBAL read_only = 1;

[mysqld]

read_only = 1

阻止所有用户执行写操作：

MariaDB [mysql]> flush tables with read lock;

2、如何保证主从复制时的事务安全？

前提：mysql对二进制日志事件数据会有缓冲;

在master上设置如下参数：

sync_binlog = 1