MySQL Master/Slave database switch records

MySQL Master/Slave database switch records

Old Master database:

Lock the table of the master database before switching to prevent data from being written to the master database during switching.

Mysql> flush tables with read lock;

Old slave database:

Mysql> show processlist;

Slave has read all relay log; waiting for the slave I/O thread to update it

Mysql> show slave status \ G
 

Old Master database:
 

Mysql> show processlist;
 

Master has sent all binlog to slave; waiting for binlog to be updated

Old slave Database

Mysql> stop slave IO_THREAD;
 

Mysql> show processlist;
 

Make sure the status is has read all relay log
 

Old Master database:
 

Mysql> show master status
 

 
Change old slave database to master database
Mysql> stop slave;

Mysql> reset master;

Mysql> reset slave;

Mysql> show master status \ G
 

Close old master database

/Etc/init. d/mysql stop

Switch the application of the master database to the slave database to disable the table lock status of the original master database.
Unlock tables;

Slave database to master database
Mysql> reset master;

Mysql> reset slave;

The master database re-creates the slave Database

New master database:

Mysql> grant replication slave on *. * to 'root' @ '192. 168.1.29 'identified by 'test123 ';

Vi/etc/my. cnf

Log-bin = mysql-masterbin

Service mysql restart

Mysql> flush tables with read lock;

Mysql> show master status; write down file and positon

/Usr/bin/mysqldump-uroot-ptest123 -- log-error = mysqldump47.log -- all-databases> mysqldump47. SQL

Mysql> unlock tables;

Scp mysqldump47. SQL 192.168.234.29:/root/

New Slave database:

Mysql> stop slave;

Mysql>/usr/bin/mysql-uroot-ptest123 <mysqldump47. SQL

Mysql> change master to master_host = '192. 168.1.47 ', master_user = 'root', master_password = 'test123', master_log_file = 'mysql-masterbin.00001 ', master_log_pos = 192;

Mysql> start slave;

Operate data on the new master database. verify the data on the new slave database.

Appendix:

Modify the configuration parameters without restarting MySQL)

System gdb-p $ (pidof mysqld)-ex "set log_bin = mysql-masterbin47"-batch
Gdb-p $ (pidof mysqld)-ex "set log_bin = mysql-masterbin47"-batch

Database read-only lock command to prevent data writing during Database Export

Flush tables with read lock;

Unlock
 

Unlock tables;

Set the data to read-only status before backup or Master/Slave switchover (users with super management permissions can still operate the table in DML)

Set global READ_ONLY = ON;

Set global READ_ONLY = OFF;

Show variables like '% read_only % ';

Set global read_only = on/off is an operation that DBA often uses: When performing Master/Slave switchover, read-only operations are generally performed on the master database first (on ), after the master-slave synchronization is complete, the slave database is set to read/write (off ). This avoids dirty data caused by dual-write during switchover.

Two variables are defined in mysqld. cc: my_bool read_only = 0, opt_readonly = 0; opt_readonly is the read_only status of the current system, and read_only is the value set to read_only.

This article permanently updates the link address: