mysql集群主备_mysql集群配置，主从复制，一主一备。

环境

主库：mysqld  Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL))       Red Hat 4.8.5-11

从库：mysqld  Ver 5.7.26-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu))

原理

场景以及问题

权限

1.最好两台数据库都搞成这个样子的权限

mysql -u root -pGRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTIONflushprivileges;

show grantsforroot;update mysql.user set authentication_string = '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' where user='root';exit;

service mysql restart;

主库配置及其操作

1.主库配置

server-id = 10log_bin= /var/log/mysql/mysql-bin.logexpire_logs_days= 10max_binlog_size=100M

#binlog_do_db=include_database_name

#binlog_ignore_db=include_database_name

#

#*InnoDB

# InnoDBis enabled by default with a 10MB datafile in /var/lib/mysql/.

#Read the manual formore InnoDB related options. There are many!

innodb_flush_log_at_trx_commit=1sync_binlog=1

2.重启，This procedure uses

mysql> FLUSH TABLES WITH READ LOCK;

3.记录二进制文件位置

mysql>show master status;+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000003 | 154 | | | |

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

1 row in set (0.00 sec)

4.主库导出数据

mysqldump -uroot -p --single-transaction --all-databases --master-data > dbdump.db

5. 解锁事务锁，On the master, released the read lock:

mysql> UNLOCK TABLES;

从库配置及其操作

1.配置

server-id               = 11

2.倒入主库转储db文件

mysql -u root -p < dbdump.db

3.指向主库，其文件名写错了以后mysql-bin.000002，照样能复制

CHANGE MASTER TO

-> MASTER_HOST='192.168.43.114',-> MASTER_USER='root',-> MASTER_PASSWORD='123456',-> MASTER_LOG_FILE='mysql-bin.000002',-> MASTER_LOG_POS=154;

4.启动从库复制线程

start slave;

5.测试成功

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event

Master_Host:192.168.43.114Master_User: root

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos:3765Relay_Log_File: bogon-relay-bin.000003Relay_Log_Pos:3978Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: Yes

Slave_SQL_Running: Yes