本文主要給大家介紹MySQL主從復(fù)制實(shí)驗(yàn)環(huán)境測試，希望可以給大家補(bǔ)充和更新些知識，如有其它問題需要了解的可以持續(xù)在創(chuàng)新互聯(lián)行業(yè)資訊里面關(guān)注我的更新文章的。

創(chuàng)新互聯(lián)建站堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都做網(wǎng)站、成都網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的淮安網(wǎng)站設(shè)計(jì)、移動媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

mysql主從復(fù)制

實(shí)驗(yàn)環(huán)境

主：192.168.110.33

從：192.168.110.59

iptables and selinux disabled

主：云服務(wù)器端

1.安裝并初始化

# yum install -y mysql -mysql-server

#/etc/init.d/mysql start

# mysql_secure_installation #設(shè)置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.用戶、修改/etc/my.cnf文件

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

59 binlog-do-db = test #指定同步test數(shù)據(jù)庫

60 binlog-ignore-db = mysql #避免同步 mysql 數(shù)據(jù)庫,以免不必要的麻煩

3.創(chuàng)建同步賬戶并授權(quán)

# mysql -p密碼

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.備份test數(shù)據(jù)庫并發(fā)送二進(jìn)制日志到從

[root@cyl_test1 mysql]# pwd

/var/lib/mysql

# file mysql-bin.000003

mysql-bin.000003: MySQL replication log

# mysqldump -p'cyl&647308' test > test.sql或

#mysqldump -p'數(shù)據(jù)庫密碼' test > test.sql

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

從:客戶端

1.安裝、初始化、修改/etc/my.cnf文件

# yum install -y mysql -mysql-server

#/etc/init.d/mysql start

# mysql_secure_installation #設(shè)置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.用戶、登錄同步賬戶（用戶必須與服務(wù)端保持一致）

# 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數(shù)據(jù)庫手動創(chuàng)建

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

| Database |

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

| information_schema |

| mysql |

| test |

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

mysql> quit

Bye

3.將數(shù)據(jù)庫導(dǎo)入，登錄數(shù)據(jù)庫看到數(shù)據(jù)同步成功（在服務(wù)端發(fā)送二進(jìn)制文件的位置）

# mysql -p'cyl&647308' test

#mysql -p'數(shù)據(jù)庫密碼' test < test.sql

# mysql -p密碼

mysql> use test;

mysql> show tables;

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

| Tables_in_test |

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

| mysql |

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

mysql> select * from mysql;

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

| username | password |

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

| girl | 123 |

| boy | 123 |

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

主：服務(wù)端

在服務(wù)端查看master的狀態(tài)

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

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

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

從：客戶端

在客戶端設(shè)置允許服務(wù)端同步數(shù)據(jù)

# 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端授權(quán)的同步用戶

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

主：服務(wù)端

改動數(shù)據(jù)庫數(shù)據(jù)觀察從是否同步

# mysql -p密碼

mysql> use test;

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

mysql> select * from mysql;

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

| username | password |

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

| girl | 123 |

| boy | 123 |

| love | 123 |

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

從：客戶端

# mysql -p密碼

mysql> use test;

mysql> select * from mysql;

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

| username | password |

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

| girl | 123 |

| boy | 123 |

| love | 123 |

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

主：服務(wù)端

mysql> delete from mysql where username='love';

從：客戶端

mysql> select * from mysql;

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

| username | password |

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

| girl | 123 |

| boy | 123 |

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

看了以上關(guān)于mysql主從復(fù)制實(shí)驗(yàn)環(huán)境測試，希望能給大家在實(shí)際運(yùn)用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補(bǔ)充的地方，如有需要更加專業(yè)的解答，可在官網(wǎng)聯(lián)系我們的24小時(shí)售前售后，隨時(shí)幫您解答問題的。