Production environment MySQL database master slave recovery from database

System hard disk damage, full reload

Faulty machine: 172.16.100.32

Recovery of the main machine according to; 172.16.100.31

1.

After the reload is complete, copy the Master Master Library Vs/program directory and empty the database data directory.

Make sure that the MySQL directory has an empty data directory, and that the owner group of the entire directory is Clouder

Chown-r clouder.clouder/home/clouder/vs/program/mysql/data/

2. Initializing MySQL database with Clouder user

scripts/mysql_install_db--user=clouder--basedir=/home/clouder/vs/program/mysql--datadir=/home/clouder/vs/ Program/mysql/data/pmp

3.

Modify the master configuration file master.cfg, just modify the Server-id to

Server-id = 112

4.

5.mysqldump Logical BACKUP Database

Log in to the main library and dump all the databases.

Mysqldump-h172.16.254.117-uroot-p5505-pengine2ocean-r--all-databases-r--master-data=2--flush-logs >/home/clo Uder/alldatabase ' Date +%f-%h-%m-%s '. sql

--all-databases: Backed up SQL statement containing build-up statements

-r: Stored procedures and triggers

6.

1 Recovering from a library

Start the service

/home/clouder/vs/program/mysql/bin/mysqld--DEFAULTS-FILE=/HOME/CLOUDER/VS/PROGRAM/MYSQL/CONF/MASTER.CNF &

1) Login

Mysql-uroot-p

2) Temporarily turn off the binary log

mysql> SET sql_log_bin = 0

3) Restore the database

Mysql> Source/home/clouder/backup/alldatabase2017-04-17-14-55-08.sql

4) Open Binary log

mysql> SET sql_log_bin = 1;

mysql> flush Privileges;

1) View the binary log files and their corresponding locations at the beginning of the mysqldump SQL file

--Change MASTER to master_log_file= ' mysql-bin.000005 ', master_log_pos=107;

2) Establish Master-slave replication

Change Master to master_host= ' 172.16.100.31 ', master_user= ' slave ', master_password= ' engine ', master_port=5505, master _log_file= ' mysql-bin.000005 ', master_log_pos=107;

3) Open Binary log

Start slave;

mysql> SET sql_log_bin = 1;

mysql> flush Privileges;

Production environment MySQL database master slave recovery from database