MySQL Master-slave replication

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.