Shulou(Shulou.com)06/01 Report--

This article mainly introduces the mysql master-slave replication experimental environment test, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

Mysql master-slave replication

Experimental environment

Master: 192.168.110.33

From: 192.168.110.59

Iptables and selinux disabled

Master: cloud server side

1. Install and initialize

# yum install-y mysql- mysql-server

# / etc/init.d/mysql start

# mysql_secure_installation # set root password

Set root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

Remove anonymous users? [Y/n] y

Disallow root login remotely? [Y/n] y

Remove test database and access to it? [Y/n] y

Reload privilege tables now? [Y/n] y

two。 User, modify / etc/my.cnf file

# id mysql

Uid=27 (mysql) gid=27 (mysql) groups=27 (mysql)

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

57 server-id = 1 # id unique

59 binlog-do-db = test # specify synchronous test database

60 binlog-ignore-db = mysql # avoid synchronizing mysql databases to avoid unnecessary trouble

3. Create a synchronization account and authorize

# mysql-p password

Mysql > create database test

Query OK, 1 row affected (0.00 sec)

Mysql > use test

Mysql > create table mysql (username varchar (25) not null, password varchar (25) not null)

Mysql > desc mysql

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | username | varchar (25) | NO | | NULL |

| | password | varchar (25) | NO | | NULL |

+-+ +

Mysql > insert into mysql value ('girl',123)

Mysql > insert into mysql value ('boy',123)

Mysql > select * from mysql

+-+ +

| | username | password |

+-+ +

| | girl | 123 | |

| | boy | 123 | |

+-+ +

Mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER ON *. * TO cyl@'192.168.110.%' IDENTIFIED BY 'cyl&647308'

Mysql > flush privileges

Mysql > quit

Bye

3. Back up the test database and send binary logs to the

[root@cyl_test1 mysql] # pwd

/ var/lib/mysql

# file mysql-bin.000003

Mysql-bin.000003: MySQL replication log

# mysqldump-paired cylwings 647308 'test > test.sql or

# mysqldump-p 'database password' test > test.sql

# scp test.sql mysql-bin.000003 192.168.110.59:

From: client

1. Install, initialize, modify / etc/my.cnf files

# yum install-y mysql- mysql-server

# / etc/init.d/mysql start

# mysql_secure_installation # set root password

Set root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

Remove anonymous users? [Y/n] y

Disallow root login remotely? [Y/n] y

Remove test database and access to it? [Y/n] y

Reload privilege tables now? [Y/n] y

# vim / etc/my.cnf

Server-id = 2 # id unique

two。 User, login synchronization account (the user must be consistent with the server)

# id mysql

Uid=27 (mysql) gid=27 (mysql) groups=27 (mysql)

# mysql-ucyl-paired cyclists 647308'- h 192.168.110.33

Mysql > show databases; # if there is no test database to create manually

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | test |

+-+

Mysql > quit

Bye

3. Import the database and log in to the database to see that the data synchronization is successful (the location where the binaries are sent on the server)

# mysql-paired cylwings 647308' test use test

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | mysql |

+-+

Mysql > select * from mysql

+-+ +

| | username | password |

+-+ +

| | girl | 123 | |

| | boy | 123 | |

+-+ +

Master: server

Check the status of master on the server

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000003 | 1834 | test | mysql | |

+-+

From: client

The client setting allows the server to synchronize data

# mysql-p

Mysql > stop slave

Mysql > change master to master_host='192.168.110.33', master_user='cyl',master_password='cyl&647308', master_log_file='mysql-bin.000003',master_log_pos=1834; # this user is a synchronous user authorized by the master side

Mysql > start slave

Mysql > show slave status\ G

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.110.33

Master_User: cyl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 1834

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Master: server

Change the database data to see if it is synchronized.

# mysql-p password

Mysql > use test

Mysql > insert into mysql value ('love',123)

Mysql > select * from mysql

+-+ +

| | username | password |

+-+ +

| | girl | 123 | |

| | boy | 123 | |

| | love | 123 | |

+-+ +

From: client

# mysql-p password

Mysql > use test

Mysql > select * from mysql

+-+ +

| | username | password |

+-+ +

| | girl | 123 | |

| | boy | 123 | |

| | love | 123 | |

+-+ +

Master: server

Mysql > delete from mysql where username='love'

From: client

Mysql > select * from mysql

+-+ +

| | username | password |

+-+ +

| | girl | 123 | |

| | boy | 123 | |

+-+ +

Read the above about the mysql master-slave replication experimental environment test, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.