MySQL Master-slave replication

MySQL Master-slave replication

Experimental environment

Master: 192.168.110.33

From: 192.168.110.59

Iptables and SELinux disabled

Master: Server-side

1. Install and initialize

# yum Install-y mysql-mysql-server

#/etc/init.d/mysql start

# mysql_secure_installation #设置root密码

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

2. 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

Server-id = 1 #id唯一

binlog-do-db = Test #指定同步test数据库

binlog-ignore-db = mysql #避免同步 mysql database to avoid unnecessary hassle

3. Create a sync 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) NOT NULL, password varchar (+) 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 [e-mail protected] ' 192.168.110.% ' identified by ' cyl&647308 ';

mysql> flush Privileges;

Mysql> quit

Bye

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

[Email protected]_test1 mysql]# pwd

/var/lib/mysql

# file mysql-bin.000003

Mysql-bin.000003:mysql Replication Log

# mysqldump-p ' cyl&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, and modify the/etc/my.cnf file

# yum Install-y mysql-mysql-server

#/etc/init.d/mysql start

# mysql_secure_installation #设置root密码

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唯一

2. User, login Sync account (the user must be consistent with the server)

# ID MySQL

uid=27 (MySQL) gid=27 (MySQL) groups=27 (MySQL)

# mysql-ucyl-p ' cyl&647308 '-H 192.168.110.33

mysql> show databases; #若没有test数据库手动创建

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

| Database |

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

| Information_schema |

| MySQL |

| Test |

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

Mysql> quit

Bye

3. Import the database, log in to the database to see the data synchronization success (where the server sends the binaries)

# mysql-p ' cyl&647308 ' test<test.sql or

#mysql-P ' database password ' test < Test.sql

# mysql-p Password

mysql> use test;

Mysql> Show tables;

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

| Tables_in_test |

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

| MySQL |

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

Mysql> select * from MySQL;

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

| Username | password |

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

| Girl | 123 |

| Boy | 123 |

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

Main: Service Side

View the status of master on the server side

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

| mysql-bin.000003 | 1834 | Test | MySQL |

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

From: Client

Allow server-side synchronization of data on client settings

# 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; #此用户为master端授权的同步用户

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

Main: Service Side

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 |

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

Main: Service Side

Mysql> Delete from the mysql where username= ' love ';

From: Client

Mysql> select * from MySQL;

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

| Username | password |

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

| Girl | 123 |

| Boy | 123 |

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

This article is from the "True Water No Fragrance" blog, please be sure to keep this source http://chengyanli.blog.51cto.com/11399167/1846794

MySQL Master-slave replication