MySQL replication problem

Today again committed two, incredibly the copy of MySQL to forget, also drunk, summed up MySQL master and slave:

The company's DB01-FK (172.33.4.155) machine is out of the question and needs to be replaced by a new machine. MySQL on this machine is also copied from the Chinese db01, so the solution: 1, from China DB01 Backup database, and the backup file to Fk;2, on the FK site new DB01-FK Restore the database, and set the master address and related parameters, to achieve replication;

Here's how:

1, CN db01 Backup database, and transfer to the new DB01-FK, operation as follows:

[Email protected] ~]# mysqldump-a--master-data=1--single-transaction--events | gzip >/TMP/FULLDB-16-12-05.SQ L.gz

[Email protected] ~]# ll-h/tmp/fulldb-16-12-05.sql.gz

-rw-r--r--1 root root 411M Dec 5 15:15/tmp/fulldb-16-12-05.sql.gz

[Email protected] ~]# AWS S3 Cp/tmp/fulldb-16-12-05.sql.gz s3://yeecalllogs-mb/

Upload:.. /tmp/fulldb-16-12-05.sql.gz to S3://yeecalllogs-mb/fulldb-16-12-05.sql.gz

[Email protected] ~]#

2, in DB01-FK (new) Copy the original DB01-FK configuration file, key file, start the service, set the remote master host related parameters

[Email protected] ~]# rsync-avzoptgl--progress 172.33.4.155:/home/mysql//home/mysql/

[Email protected] ~]# RSYNC-AVZOPTGL--progress 172.33.4.155:/etc/my.cnf/etc/my.cnf

[[email protected] ~]# service mysqld start

[[email protected] ~]# MySQL

mysql> Change Master to

master_host= ' 1.1.1.1 ',

master_user= ' Repl ',

master_password= ' 123456789 ',

master_port=13306,

master_ssl=1,

master_ssl_ca= '/home/mysql/ssl/ca.crt ',

master_ssl_cert= '/home/mysql/ssl/4.idc.ycall.com.crt ',

- master_ssl_key= '/home/mysql/ssl/4.idc.ycall.com.key ';

Query OK, 0 rows affected (0.02 sec)

Mysql> Exit

Restore the database on DB01-FK (new) for replication

[Email protected] ~]# AWS S3 CP s3://yeecalllogs-mb/fulldb-16-12-05.sql.gz/tmp/

Download:s3://yeecalllogs-mb/fulldb-16-12-05.sql.gz to. /tmp/fulldb-16-12-05.sql.gz

[Email protected] ~]# gzip-d/tmp/fulldb-16-12-05.sql.gz

[Email protected] ~]# grep-p-io "Change Master to. *\d;$"/tmp/fulldb-16-12-05.sql

Change MASTER to master_log_file= ' mysql-bin.000069 ', master_log_pos=8105870;

[[email protected] ~]# MySQL </tmp/fulldb-16-12-05.sql

[[email protected] ~]# MySQL

mysql> start slave;

Mysql> show slave status \g

[Email protected] ~]#

Description

Mysqldump--help

--master-data[=#] This causes the binary log position and filename to be

appended to the output. If equal to 1, would print it as a

Change MASTER command; If equal to 2, that command would

Be prefixed with a comment symbol. This option would turn

--lock-all-tables on, unless--single-transaction is

Specified too (in which case a global read lock are only

Taken a short time at the beginning of the dump; Don ' t

Forget to read about--single-transaction below). In all

Cases, any action on logs would happen at the exact moment

of the dump. Option automatically turns--lock-tables

Off.

--master-data[=#] Append the location and name of the binary Binlog file in the backup exported file

if the value equals 1, a change MASTER statement is added

if the value equals 2, a comment is added before the change MASTER statement

This parameter--lock-all-tables the lock table unless--single-transaction is specified.

in this case, the lock table will only last for a short period of time at the start of the dump, supposedly

at dump , any action will affect the binlog file .

After dump is finished, the option automatically turns off the lock table function

    Analysis: class= Port If you specified   change master to  db Db   so  dump  file with   Master_log_file   master_log_pos master host is modified (even if the values before and after the modification are the same) , a new file and pos , the statement that originally dump file was flushed out. so this way you must first specify host , then restore, and finally start slave; 

of course: last time I did this, I did not add these two parameters at mysqldump , using the -X Lock table function, but in the mysqldump process, show Master on the main library. Status, view position and logfile, and then note the two values to specify these parameters when you change from the library to the master to operation.

This article is from the "11462293" blog, please be sure to keep this source http://11472293.blog.51cto.com/11462293/1881451

MySQL replication problem