MySQL series E-2------mysql master-slave replication combat

1 , change master and slave configuration files

Master configuration file

# CP/MYSQL/SUPPOTS-FILE/MY-DEFAULT-CNF

[Client]

port=

socket=

[Mysqld]

Basedir=

Datadir=

port=

Server-id=

Socket=/data/mysql.sock

Log-bin=/data/mysql_bin

Binlog_format=mixed/row/sql

binlog-do-db=

binlog-ignore-db=

Long_query_time=1

Relay-log=/data/relay_bin

Relay-log-info-file=/data/relay_log.info

[Mysqld_safe]

Log-error=/data/mysql_err.log

Pid-file=/data/mysql_pid.pid

Slave configuration file

# CP/MYSQL/SUPPOTS-FILE/MY-DEFAULT-CNF

[Client]

port=

socket=

[Mysqld]

Basedir=

Datadir=

port=

Server-id=

Socket=/data/mysql.sock

Log-bin=/data/mysql_bin

Binlog_format=mixed/row/sql

binlog-do-db=

binlog-ignore-db=

Long_query_time=1

Relay-log=/data/relay_bin

Relay-log-info-file=/data/relay_log.info

[Mysqld_safe]

Log-error=/data/mysql_err.log

Pid-file=/data/mysql_pid.pid

2 , open the binary log log-bin=

3 , set different server-id=

4 , authorized master-slave copy of the user password

mysql> grant replication slave on*.* to ' rep ' @ ' 192.168.1.101 ' identified by ' Oracle ';

mysql> flush Privileges;

5 , lock table

Mysql> Flush table with read lock;

6 , reopen a window (second window) to make a backup

Mysqldump-h127.0.0.1-uroot-poracle--port=3307-s/data/mysql.sock-a-B--events-x--master-data=1 |gzip>/opt/rep. Sql.gz

Attention:

-A back up all

- b The command to generate the CREATE table in the backup file, which does not require the database to be specified when recovering

- x Lock Table

--master-data=1 You do not need to see the binaries and locations through show master status, they will be recorded in the backup file, and if the binaries are not deleted, you can always do so from the library

7 , unlocking in the first window

Unlock tables;

8 , make change master to operation on slave

Change Master to

Master_host= ' 192.168.1.102 ',

master_port=3307,

-Master_user= ' rep ',

-master_password= ' Oracle ',

->master_log_file= ' mysqlbin.000003 ',

->master_log_pos=408;

If the--master-data=1 parameter is used when backing up, then there is no need for the last two sentences:

The information listed above is stored in the master.info of the Slave database directory, and is viewed in the following:

[email protected] 3308]# LL

Total 110660

-RW-RW----. 1 MySQL MySQL 07:14 auto.cnf

-RW-RW----. 1 mysql mysql 12582912 Jun17 13:23 ibdata1

-RW-RW----. 1 mysql mysql 50331648 Jun17 13:23 ib_logfile0

-RW-RW----. 1 mysql mysql 50331648 Jun17 07:05 ib_logfile1

-RW-RW----. 1 MySQL MySQL, June 15:25 Master.info

DRWX------. 2 MySQL mysql 4096 June 07:05 MySQL

-RW-RW----. 1 MySQL MySQL 5 June 13:23 Mysql03.pid

-rw-r-----. 1 MySQL MySQL 40502 June 15:25 Mysql_err.log

DRWX------. 2 MySQL mysql 4096 June 07:05 Performance_schema

DRWX------. 2 MySQL mysql 4096 June 07:05 test

[email protected] 3308]# cat Master.info

23

mysqlbin.000003

408

192.168.1.102

Rep

Oracle

3307

60

0

9 , turn on replication

mysql> start slave;

Query OK, 0 rows affected (0.05 sec)

This article from "Technology bo" blog, declined reprint!

MySQL series E-2------mysql master-slave replication combat