Two-way master-slave and keepalived high availability for Mysql

Recently, I was working on a two-way master-slave MySQL database. I learned that keepalived can automatically judge and switch to the available database. I tried it and sorted out the document...

Recently, I was working on a two-way master-slave MySQL database. I learned that keepalived can automatically judge and switch to the available database. I tried it and sorted out the document...

Recently, I was working on a two-way master-slave Mechanism for the MySQL database. I learned that keepalived can automatically judge and switch to the available database. I tried it and sorted out the document.

Declare the next environment first

Iptables enables or disables port 3306 and selinux

MySQL-01: 192.168.204.138

MySQL-02: 192.168.204.139

VIP: 192.168.204.200 # ip address connected to the web server. You can use the tool to connect to it.

The Installation Process of MySQL is skipped. you can install MySQL as needed.

1. Modify the database configuration file/etc/my. cnf:

1.1 modify the database file for the MySQL-01 and add the following content under [mysql]

Server_id = 1log_bin = mysql-bin

1.2 modify the database file for the MySQL-02 and add the following content under [mysql]

Server_id = 2log_bin = mysql-bin

2. Set up one-way Master/Slave

2.1 On MySQL-01

2.1.1 operation authorization

Mysql-u root-p # enter the password mysql> grant replication slave on *. * to 'slave '@ '192. 168.204.139' identified by '20140901'; mysql> flush privileges;

2.1.2 data passed to MySQL-02

Mysql-u root-p # enter the password flush tables with read lock; # lock the table to operate mysql> show master status; + metric + ---------- + -------------- + ---------------- + metric + | File | Position | Binlog_Do_DB | metric | usage | + ------------------ + ---------- + -------------- + metric + mysql-bin.000012 | 120 | | + ------------------ + ---------- + -------------- + -------------------- + ------------------- + 1 row in set (0.00 sec) mysqldump-u root-p -- all-databases> mysqldump. sqlmysql-u root-pmysql> unlock tables; mysql> quit scp myqsldump. SQL 192.168.204.139:/root/

2.2 operate on MySQL-02

2.2.1 Restore database data

Mysql-u root-p <mysqldump. SQL

2.2.2 establish master-slave Synchronization

Mysql-u root-pmysql> change master to master_host = '2017. 168.204.138 ', master_user = 'slave', master_password = '000000', master_log_file = 'mysql-bin.000012 ', master_log_pos = 123456, master_port = 120; start slave;

Check whether it is successful

Show slave status \ G; Slave_IO_Running: YesSlave_ SQL _Running: Yes

Yes indicates that the synchronization is successful.

Note: master_port = 3306. This option is not required by default, but is required if you have modified the port.

3. Build a master-slave relationship

3.1 operate on MySQl-01

3.1.1 user authorization

Mysql-u root-p123456mysql> grant replication slave on *. * to 'slave '@ '192. 168.204.138' identified by '123456'; // create a permission for mysql> flush privileges;

Note: because all databases are master-slave, the operations on the MySQL-01 will be synchronized to the MySQL-02.

3.2 operations on MySQL-02

Mysql-u root-pmysql> show master status \ G; + metric + ---------- + -------------- + ---------------- + metric + | File | Position | Binlog_Do_DB | metric | usage | + ------------------ + ---------- + -------------- + metric + mysql-bin.000009 | 120 | | + ------------------ + ---------- + -------------- + -------------------- + ------------------- + 1 row in set (0.00 sec)