mysql-5.5 Configuring Master-Slave and principal-principal relationships

MySQL has a particularly high chance of downtime in the production environment, so we only install MySQL on the machine and not use it. The database itself is also a very easy place to create bottlenecks. Master: Master (for writing data) Slave: from (for reading data) is also an important step to achieve read and write separation. A lot of domestic video live website, Bilibili and other bomb screen category of Web site, in the video frame made a transparent bomb screen, background database every second tens of thousands of read and write operations, backstage OPS staff really want to die heart have ... Ha ha.. Don't say much nonsense, start deploying ...

Pre-Preparation steps: Turn off SELinux, shut down the firewall, and compile the mysql5.5

Lab environment: Centos6.5 ip:192.168.1.1 MySQL Master

Centos6.5 ip:192.168.1.2 MySQL from

Configure the Primary

[[email protected] ~]# mysql-u root-predhat//mysql login user and password

[Email protected] ~]# show dabaseses; Display Database

[[email protected] ~]# use MySQL; Using the MySQL Library

[Email protected] ~]# show tables; Show Table

[Email protected] ~]# desc user; View the structure of the user table

[Email protected] ~]# Select Host,user,password form Mysql.user; Query host in user table, password field

[[email protected] ~]# insert INTO Mysql.user (Host,user,password) VALUES (' localhost ', ' Baidu ', Password (' 123.com ')); Insert a piece of data, user Baidu, password 123.com

[[Email protected] ~]# grant replication Slave on * * to ' Baidu ' @ ' 192.168.1.2 ' identified by ' 123.com ' with GRANT option; Authorized to 192.168.1.2 Users can use Baidu This user, password 123.com to do the copy operation

[Email protected] ~]# CREATE DATABASE test; Create a library of tests with the library name test

[Email protected] ~]# VIM/ETC/MY.CNF

About 49 Lines Log-bin=mysql-bin

Added: Binlog-do-db=test//write test library to binary log

Binlog-ignore-db=mysql//Ignore MySQL library write binary log

Modified: Server-id = 1 The value of the server ID of each master from or primary should be unique.

[Email protected] ~]#/etc/init.d/mysqld restart

[Email protected] ~]# mysql-u root-predhat

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      mysql-bin.000005 | 107 | Test | MySQL |

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

1 row in Set (0.00 sec)//above two values will be used from MySQL:

The following configuration from MySQL

[Email protected] ~]# VIM/ETC/MY.CNF

About 49 Lines Log-bin=mysql-bin

Added: replicate-do-db=test//copy of library name test

Replicate-ignore-db=mysql//non-replicated library name MySQL

Modified: Server-id = 2 value unique

[Email protected] ~]#/etc/init.d/mysqld restart

[Email protected] ~]# mysql-u root-predhat

[Email protected] ~]# CREATE DATABASE test; Create the same library name as the master above

[[email protected] ~]# slave stop;

[Email protected] ~]# change master to master_host= ' 192.168.1.1 ', master_user= ' Baidu ', master_password= ' 123.com ', master_log_file=' mysql-bin.000005 ', master_log_pos=107;

Specify the primary IP to copy the user and password. The above two values will change at each restart of the primary MySQL . If you restart, you will need to reset the master to.

[[email protected] ~]# slave start; Turn on replication

[Email protected] ~]# show slave status\g

Mysql> Show Slave Status\g

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

Slave_io_state:waiting for Master to send event

master_host:192.168.1.1

Master_user:baidu

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000005

read_master_log_pos:107

relay_log_file:localhost-relay-bin.000003

relay_log_pos:253

relay_master_log_file:mysql-bin.000005

Slave_io_running:yes

Slave_sql_running:yes

replicate_do_db: Test

Replicate_ignore_db:mysql

Two yes indicates correct master/slave configuration ...

Next, we'll create a table above the main MySQL test library and see if it syncs from MySQL.

Master MySQL

mysql> use test;

Database changed

Mysql> CREATE TABLE student (ID int (one) not NULL, PRIMARY key (ID));

Query OK, 0 rows affected (0.06 sec)

Mysql> Show tables;

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

| Tables_in_user |

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

| Student |

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

1 row in Set (0.00 sec)

from MySQL

mysql> use test;

Database changed

Mysql> Show tables;

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

| Tables_in_user |

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

| Student |

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

1 row in Set (0.00 sec)

You can see that the student table has been successfully synchronized, so this MySQL master-slave experiment has been completed.

PS: Common error, firewall blocked port, authorization password filling error Mysql-bin error every time restart service will change changes mater to need to re-knock.

MySQL compiled installation has a small bug, no matter what error causes the MySQL Restart failure will report PID loss, usually your configuration file is wrong, or find the log file for error.

This article is from the "11000174" blog, please be sure to keep this source http://11010174.blog.51cto.com/11000174/1901854

mysql-5.5 Configuring Master-Slave and principal-principal relationships