MySQL Master (Master) from (Slave) backup;

Lab Environment:

CentOS Release 6.6 (Final) 32-bit

master:10.10.10.11

slave1:10.10.10.7

slave2:10.10.10.8

1) MySQL installation ( run the following order in Master,slave1,slave2, respectively )

[Email protected] ~]# yum install mysql-server mysql-y

[[email protected] ~]# service mysqld start

2) configuration my.cnf

[[email protected] ~]# mysqladmin-u root password 123.com//modify MySQL root password to "123.com"

[[email protected] ~]# VI/ETC/MY.CNF//Modify MY.CFG configuration file, add the following content;

The relevant information is as follows:

#Master

[Mysqld]

Log-bin=mysql-bin//binary file

server-id=1 //service ID (unique)

#Slave1

[Mysqld]

Log-bin=mysql-bin//binary file

server-id=2 //service ID

#Slave2

[Mysqld]

Log-bin=mysql-bin//binary file

server-id=3 //service ID

After saving exit, restart the MySQL service:

[Email protected] ~]# service mysqld restart

Stopping mysqld: [OK]

Starting mysqld: [OK]

3) Create a backup account on master;

[Email protected] ~]# mysql-u root-p123.com

Mysql> GRANT REPLICATION SLAVE on * * to ' myslave ' @ ' 10.10.10.7 ' identified by ' 123.com ';

Query OK, 0 rows affected (0.02 sec)

Mysql> GRANT REPLICATION SLAVE on * * to ' myslave ' @ ' 10.10.10.8 ' identified by ' 123.com ';

Query OK, 0 rows affected (0.02 sec)

View Master Status

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      mysql-bin.000002 |              261 |                  | |

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

1 row in Set (0.00 sec)

4) set on Slave1 Slave2

[Email protected] ~]mysql-u root-p123.com

Mysql> Change Master to

Master_host= ' 10.10.10.11 ',

Master_user= ' Myslave ',

-master_password= ' 123.com ',

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=261;

Start the slave on slave1,slave2 and check the status;

mysql> slave start;

Mysql> Show Slave status \g

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:10.10.10.7

Master_user:myslave

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000002

read_master_log_pos:261

relay_log_file:mysqld-relay-bin.000004

relay_log_pos:406

relay_master_log_file:mysql-bin.000002

Slave_io_running:yes

Slave_sql_running:yes

replicate_do_db:

replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

last_errno:0

Last_error:

skip_counter:0

exec_master_log_pos:261

relay_log_space:707

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

seconds_behind_master:0

Master_ssl_verify_server_cert:no

last_io_errno:0

Last_io_error:

last_sql_errno:0

Last_sql_error:

1 row in Set (0.00 sec)

5) test Master,slave synchronization;

1. Create database information on master;

mysql> CREATE DATABASE Info;

Query OK, 1 row Affected (0.00 sec)

Mysql> Use Info

Database changed

mysql> CREATE TABLE User (id int (3), name varchar (10));

Query OK, 0 rows affected (0.04 sec)

Mysql> INSERT into user values (001, ' Zhangcheng ');

Query OK, 1 row affected (0.01 sec)

2. View on the slave1,slave2;

mysql> show databases;

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

| Database |

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

| Information_schema |

| Ninestar |

| info |

| MySQL |

| Test |

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

5 rows in Set (0.00 sec)

Mysql> Use Info

Reading table information for completion of table and column names

Can turn off this feature to get a quicker startup with-a

Database changed

Mysql> select * from user;

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

| ID | name |

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

| 1 | Zhangcheng |

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

1 row in Set (0.00 sec)

Information is synced!

MySQL Master (Master) from (Slave) backup;