CENTOS7 Configuration Installation mysql5.5 master-slave replication (one master two from)

1: Install the mysql5.5 database

Planning:

Master: Server-1---10.64.5.167

From: server-2---10.64.5.170

From: server-3---10.64.5.172

Data Catalog:/var/lib/mysql

Download mysql5.5

#wget Http://cdn.mysql.com//Downloads/MySQL-5.5/MySQL-client-5.5.46-1.el7.x86_64.rpm#wget http://cdn.mysql.com// downloads/mysql-5.5/mysql-server-5.5.46-1.el7.x86_64.rpm

Installation

#rpm-IVH MYSQL-SERVER-5.5.46-1.EL7.X86_64.RPM#RPM-IVH mysql-client-5.5.46-1.el7.x86_64.rpm

Copy configuration file

#cp/usr/share/mysql/my-medium.cnf/etc/my.cnf

Self-booting:

#systemctl Enable Mysqld

2: Configure MySQL Main library

Master: Server-1 10.64.5.167

(1) Configure the creation of database cattle that need to be synchronized.

#mysqlmysql >createdatabase IF not EXISTS cattle collate= ' utf8_general_ci ' CHARACTER set= ' UTF8 '; Cattle.*to ' cattle ' @ '% ' identified by ' cattle ', mysql>grant all on cattle.*to ' cattle ' @ ' localhost ' identified by ' Cattl E ';

(2) Create a user

Mysql> GRANT REPLICATION slave,reload,super on * * to [e-mail protected] ' 10.64.5.170 ' identified by ' 123456 ';mysql> G RANT REPLICATION slave,reload,super On * * to [e-mail protected] ' 10.64.5.172 ' identified by ' 123456 ';mysql> flush Privi Leges;

(3) configuration file

#vim/etc/my.cnf Add Server-id = 1log-bin=mysql-binlog-slave-updatesbinlog-do-db=cattlebinlog-ignore-db=mysql

Restart MySQL

(4) Lock main Library table

Mysql> FLUSH TABLES with READ LOCK;

(4) Display the main library information

mysql> SHOW MASTER STATUS;

(5) Open a different terminal, package the main library

#cd/var/lib/mysql#tar czvf cattle.tar.gz Cattle

(6) Unlock the main library table

Mysql> UNLOCK TABLES;

3: Configure MySQL from library

From: Server-2 10.64.5.170

From: Server-3 10.64.5.172

(1) Transfer the cattle.tar.gz to the Slav machine

#mv cattle.tar.gz/var/lib/mysql/#cd/var/lib/mysql/#tar XF cattle.tar.gz

(2) View Modify Cattle folder permissions

#chown-R Mysql:mysql Cattle

(3) configuration file

#vim/etc/my.cnf------------server-2 add server-id =2log_bin = Mysql-binrelay_log = mysql-relay-binread-only= 1replicate-do-db=cattlelog-slave-updates=1

------------server-3 Add Server-id =3log_bin = Mysql-binrelay_log = mysql-relay-binread-only=1replicate-do-db= Cattlelog-slave-updates=1

Restart the slave MySQL

(4) Verifying the connection

Test the connection main library from the library server-2

#mysql-h10.64.5.167-uslave1-p123456mysql> Show grants for [email protected];

Test the connection main library from the library server-3

# mysql-h10.64.5.167-uslave2-p123456mysql> show grants for [email protected];

(5) Set slave replication

Query the position value of master

mysql> SHOW MASTER status\g;*************************** 1. Row *************************** file:mysql-bin.000001 position:120 Binlog_do_db:cattlebinlog_ignor E_DB:MYSQL1 row in Set (0.00 sec) Error:no query specified

server-2 configuration mysql>change master to master_host= ' 10.64.5.167 ',          -> master_user= ' slave1 ',          -> master_password= ' 123456 ',          -> master_log_file= ' mysql-bin.000001 ',          -> MASTER_LOG_POS=120;     -------Master_log_ POS position 
 for the main library

Server-3 configuration Mysql>change MASTER to master_host= ' 10.64.5.167 ', master_user= ' slave2 ', MASTER_PA Ssword= ' 123456 ', master_log_file= ' mysql-bin.000001 ', master_log_pos=120;

(6) slave start

Mysql> START SLAVE; Query OK, 0 rows affected, 1 Warning (0.00 sec)

Run Show SLAVE status to view the output:

Main view

Slave_io_running:yes

slave_sql_running: yes

mysql> show slave status\g;*************************** 1. row ***************                slave_io _state: waiting for master to send event                   Master_Host: 10.64.5.167                   master_ user: slave1                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           read_master_log_pos: 120                relay_log_file: mysql-relay-bin.000002                 Relay_Log_Pos: 178995708         relay_master_log_file: mysql-bin.000001              Slave_IO_Running: Yes             slave_sql_running: yes               replicate_do_db: cattle          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: 178995562               Relay_Log_Space: 178995864               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: 0master _ssl_verify_server_cert: no                 Last_IO_Errno: 0                 Last_IO_Error:                &nbsP last_sql_errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:               Master_Server_Id: 11 row in set  (0.00  Sec

Validate master

mysql> show  processlist\g;*************************** 1. row ***************      Id: 14   User: system user    Host:      db: NULLCommand: Connect   Time:  63424  state: connecting to master   info: null******************  2. row ***************************     Id: 15    User: system user   Host:      db:  Nullcommand: connect   time: 63424  state: slave has read  all relay log; waiting for the slave I/O thread to  update it   info: null*************************** 3. row *************      id: 25   user: slave1   host:  10.64.5.170:47139     db: nullcommand: binlog dump    time: 62967  state: master has sent all binlog to slave;  waiting for binlog to be updated   info: null**************  4. row ***************************     id: 244    User: slave2   Host: 10.64.5.172:45629      db: NULLCommand: Binlog Dump   Time: 53898  State:  Master has sent all binlog to slave; waiting for binlog to  be updated   Info: NULL16 rows in set  (0.00 sec) ERROR:  no query specified

4: Verify Master-Slave synchronization

Create a table in the main library server-1

mysql> use cattle;database changedmysql> create table  ' Test '   (' name '   varchar ( null&nbsp), ' old '   char (ten)  NULL ); query ok, 0 rows affected  (0.00 sec) mysql> desc test;      +-------+-------------+------+-----+---------+-------+| field | type         | null | key | default | extra  |+-------+-------------+------+-----+---------+-------+| name  | varchar (Ten)  |  YES  |     | NULL    |        | |  old   | char (Ten)     | YES  |      | null    |       |+-------+---------- ---+------+-----+---------+-------+2 rows in set  (0.00 SEC) 

Query from library for this new table

mysql> use cattle;database changedmysql> DESC test;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name | varchar (10) |     YES | |       NULL | || Old | char (10) |     YES | |       NULL | |+-------+-------------+------+-----+---------+-------+2 rows in Set (0.01 sec)

At this point, MySQL's master-slave replication is complete.

This article is from the "8931355" blog, please be sure to keep this source http://8941355.blog.51cto.com/8931355/1712629

CENTOS7 Configuration Installation mysql5.5 master-slave replication (one master two from)