mysql主从备份不加密

方式一：主从备份不加密

主库：1.   [mysqld] #my.cnf 文件

#########

server-id=1 #主库标识

log-bin=mysql-bin #记录日志的方式  mysql 5.6版本

log_slave_updates=1

expire-logs-days=7 #日志存储时间

2.###重启mysql;创建备份的用户

mysql> CREATE USER 'slavebak'@'%' IDENTIFIED BY 'Te7OLYv4ptDrH8eVzh1h8bLhXwTPh2';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'slavebak'@'%' ;

mysql> FLUSH PRIVILEGES;

3.导出要备份的数据库

4，mysql > SHOW MASTER STATUS; #查出主库当前的备份文件和节点

mysql> SHOW MASTER STATUS;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000005 | 676 | | | |

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

1 row in set (0.00 sec)

从库： 1. [mysqld] #my.cnf 文件

####

server-id=2 #从库标识，不能和主库一样

binlog-do-db=mysql   //备份的数据库名,可以添加多个或者 

binlog-ignore-db=test //不做备份的库

2.重启mysql，导入备份的数据库

3.

mysql> CHANGE MASTER TO MASTER_HOST='192.168.6.98',master_port=3306, MASTER_USER='bak', MASTER_PASSWORD='bak', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=120; ##（LOG_FILE和LOG_POS 要以主库查询出的为准）

4. 启动Slave

mysql> start slave;

(如果需要修改 信息)

5.stop slave 停止slave,reset slave 重置slave;

再次执行  CHANGE 后 start slave

方式二：主从备份加密

主库:1.生成密钥

mkdir -p /usr/local/mysql/sslcert

cd /usr/local/mysql/sslcert

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -days 10000 -key ca-key.pem > ca-cert.pem

openssl req -newkey rsa:2048 -days 10000 -nodes -keyout server-key.pem > server-req.pem

openssl x509 -req -in server-req.pem -days 10000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

openssl req -newkey rsa:2048 -days 10000 -nodes -keyout client-key.pem > client-req.pem

openssl x509 -req -in client-req.pem -days 10000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

###

openssl rsa -in server-key.pem -out server-key.pem

tar -czvf sslcert.tar.gz sslcert  

2.

[mysqld] #my.cnf 文件

#########

server-id=1 #主库标识

log-bin=mysql-bin #记录日志的方式  mysql 5.6版本

log_slave_updates=1

expire-logs-days=7 #日志存储时间

ssl

ssl-ca=/usr/local/mysql/sslcert/ca-cert.pem

ssl-cert=/usr/local/mysql/sslcert/server-cert.pem

ssl-key=/usr/local/mysql/sslcert/server-key.pem

3.###重启mysql,然后： mysql> show variables like '%ssl%';

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

| Variable_name | Value |

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

| have_openssl | YES |

| have_ssl | YES |

| ssl_ca | /usr/local/mysql/sslcert/ca-cert.pem |

| ssl_capath | |

| ssl_cert | /usr/local/mysql/sslcert/server-cert.pem |

| ssl_cipher | |

| ssl_crl | |

| ssl_crlpath | |

| ssl_key | /usr/local/mysql/sslcert/server-key.pem |

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

9 rows in set (0.00 sec)

mysql> CREATE USER 'slavebak'@'%' IDENTIFIED BY 'Te7OLYv4ptDrH8eVzh1h8bLhXwTPh2';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'slavebak'@'%' REQUIRE SSL;

mysql> FLUSH PRIVILEGES;

4.导出数据库

5.#############

mysql> FLUSH TABLES WITH READ LOCK;

mysql > SHOW MASTER STATUS;

mysql> UNLOCK TABLES;

从库：

 1. [mysqld] #my.cnf 文件

####

server-id=2 #从库标识，不能和主库一样

binlog-do-db=mysql   //备份的数据库名,可以添加多个或者 

binlog-ignore-db=test //不做备份的库

2.重启mysql，导入备份的数据库

3.

mysql> CHANGE MASTER TO MASTER_HOST='120.25.95.207',MASTER_PORT=24306,MASTER_USER='slavebak', MASTER_PASSWORD='Te7OLYv4ptDrH8eVzh1h8bLhXwTPh2', MASTER_LOG_FILE='mysql-bin.000069', MASTER_LOG_POS=917224584, MASTER_SSL=1, MASTER_SSL_CA = '/usr/local/mysql/sslcert/ca-cert.pem', MASTER_SSL_CERT = '/usr/local/mysql/sslcert/client-cert.pem', MASTER_SSL_KEY = '/usr/local/mysql/sslcert/client-key.pem';

4. 启动Slave

mysql> start slave;

(如果需要修改 信息)

5.stop slave 停止slave,reset slave 重置slave;

再次执行  CHANGE 后 start slave