MySQL Master-slave configuration

Host role:

Primary database:

[Email protected] ~]# hostname

A-db01

[[email protected] ~]# ifconfig eth0 |awk-f ' [:]+ ' Nr==2{print $4} '

172.16.0.14

From the database:

[Email protected] ~]# hostname

A-db02

[[email protected] ~]# ifconfig eth0 |awk-f ' [:]+ ' Nr==2{print $4} '

172.16.0.15

Main Library Operations:

1. Main Library open binlog function

[[email protected] ~]# grep "Log-bin"/etc/my.cnf

Log-bin=mysql-bin

2. Make sure the Server-id is different

[[email protected] ~]# grep server-id/etc/my.cnf

Server-id = 1

3. The Main Library to create a synchronous account rep

mysql> grant replication Slave on * * [email protected] "172.16.0.%" identified by ' Lqc123 ';

Query OK, 0 rows Affected (0.00 sec)

To view authorized permissions:

Mysql> Show grants for [email protected] ' 172.16.0.% ';

mysql> flush Privileges;

Query ok,0 Rows Affected (0.00 sec)

4. lock table, maintain data consistency

Flush table with read lock;

After the lock table, re-open the window, operation.

Backup

[Email protected] ~]# mysqldump-uroot-p123456 >/root/rep_bak.sql

Pushed to the slave library.

From library operations:

1. Make sure all Server-id are different

[[email protected] ~]# grep server-id/etc/my.cnf

Server-id = 2

2. fully-prepared the master library from the library

[Email protected] ~]# mysql-uroot-p123456 </root/rep_bak.sql

Recovery Success >

3. Locate the location, configure Master.info

View location points on the main library

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|     mysql-bin.000004 |              1416 |                  | |

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

1 row in Set (0.00 sec)

mysql-bin.000004 | 1416

When you create from a library

Change MASTER to

Master_host= ' 172.16.0.14 ',

master_port=3306,

Master_user= ' rep ',

Master_password= ' Lqc123 ',

Master_log_file= ' mysql-bin.000004 ',

master_log_pos=1416;

mysql> start slave;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show Slave Status\g

1. row***************************

Slave_io_state:waiting for Master to send event

master_host:172.16.0.14

Master_user:rep

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000004

read_master_log_pos:1416

relay_log_file:a-db02-relay-bin.000002

relay_log_pos:569

relay_master_log_file:mysql-bin.000004

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:1416

relay_log_space:726

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:

Replicate_ignore_server_ids:

Master_server_id:1

1 row in Set (0.00 sec)

if The change master is set incorrectly, modify Master Info does not take effect , please perform

Reset slave All;

This article is from the "Tianshui line" blog, please be sure to keep this source http://zhouzefang.blog.51cto.com/9475331/1760709

MySQL Master-slave configuration