MySQL's master-slave replication and dual master model

First, the IP address of the master-slave server

Master ip:172.16.1.1

Salve ip:172.16.1.64

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/F6/wKioL1Qf6-Twg6x9AAKZqVU98Co146.jpg "title=" Topology diagram. png "alt=" wkiol1qf6-twg6x9aakzqvu98co146.jpg "/>

Second, install MySQL and configure Master and slave.

1. Install Musql Server

#yum Install Mysql-server-y

2. Create MySQL data storage location

Mkdir/mydata/data-pv

Mkdir/mydata/binlogs-pv

MKDIR/MYDATA/REAYLOGS-PV (created from the server)

3, the owner of the modified directory, belong to the group

Chown-r mysql.mysql/mydata/*

Mster Server:

#vim/etc/mysql/my.cnf

DataDir =/mydata/data

Log-bin=/mydata/binlogs/master-bin

Salve Server:

Vim/etc/mysql/my.cnf

DataDir =/mydata/data

Relay-log =/mydata/reaylogs/relay-log

#log-bin=mysql-bin

#binlog_format =mixed

Server-id = 11

The Server-id value of the master-slave server is different

Third, create a connection master account for Slave:

MariaDB [(None)]> grant replication Slave, replication client on * * to [e-mail protected] ' 172.16.%.% ' identified by ' Ma Geedu ';

MariaDB [(None)]> flush privileges;

To view the status information for the master node:

MariaDB [(None)]> Show Master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      master-log.000007 |              245 |                  | |

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

Iv. connect the Master on the Salev:

1, when connecting to the main server if the report:

MariaDB [(None)]> change MASTER to master_host= ' 172.16.1.1 ', master_user= ' repluser ', master_password= ' mageedu ', Master_log_file= ' master-log.000007 ', master_log_pos=245;

ERROR 1201 (HY000): Could not initialize master info structure; More error messages can is found in the MariaDB error log

Workaround:

MariaDB [(none)]> reset slave;

Query OK, 0 rows Affected (0.00 sec)

MariaDB [(None)]> change MASTER to master_host= ' 172.16.1.1 ', master_user= ' repluser ', master_password= ' mageedu ', Master_log_file= ' master-log.000007 ', master_log_pos=245;

View from node State:

MariaDB [(none)]> show Slave status\g

2. Start Io_thread, Sql_thread thread:

MariaDB [(None)]> START SLAVE io_thread;

MariaDB [(None)]> START SLAVE sql_thread;

or use

MariaDB [(none)]>start SLAVE;

3. View the slave node status again:

MariaDB [(None)]> SHOW SLAVE status\g

Compare the differences before and after starting slave

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/F5/wKioL1Qf6e7BFMrgAAI5GjHr6wI341.jpg "style=" float: none; "title=" Noslave.png "alt=" Wkiol1qf6e7bfmrgaai5gjhr6wi341.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/F4/wKiom1Qf6c_x_LmoAAKJynOd4Wg996.jpg "style=" float: none; "title=" Startslave.png "alt=" Wkiom1qf6c_x_lmoaakjynod4wg996.jpg "/>

To create a database on the primary node

MariaDB [(None)]>create database NAME;

On the From node view:

MariaDB [(none)]>show databases;

#################################################################

############## #补充部分 ############################################

1. Stop copy function method from node:

MariaDB [(none)]> stop slave;

2. If you join from the server, you need to import data from master

On master: #mysqldump--all-databases--lock-all-tables--routines--triggers--master-data=2--flush-logs >/tmp/ All.sql

On the backup data import to Slave: #scp/tmp/all/sql [Email protected]:/tmp

After you import the backup file, do the following slave:

#mysql </tmp/all.sql

3. How to restrict read-only from the server?

MariaDB [mysql]> SET GLOBAL read_only = 1;

*************************

[Mysqld]

READ_ONLY = 1

4. Prevent all users from performing write operations:

MariaDB [mysql]> flush tables with read lock;

5, how to ensure that the master-slave replication transaction security?

Premise: MySQL will buffer the binary log event data;

Set the following parameters on master:

Sync_binlog = 1

This article from the "rookie ahead of the road" blog, declined reprint!

MySQL's master-slave replication and dual master model