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

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

mysql主從復制

實驗環(huán)境

主：192.168.110.33

從：192.168.110.59

iptables and selinux disabled

主：云服務器端

1.安裝并初始化

# 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.用戶、修改/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)建同步賬戶并授權

# 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ā)送二進制日志到從

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

# 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ù)庫導入，登錄數(shù)據(jù)庫看到數(shù)據(jù)同步成功（在服務端發(fā)送二進制文件的位置）

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

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

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

主：服務端

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

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

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

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

從：客戶端

在客戶端設置允許服務端同步數(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端授權的同步用戶

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

主：服務端

改動數(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 |

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

主：服務端

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

從：客戶端

mysql> select * from mysql;

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

| username | password |

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

| girl | 123 |

| boy | 123 |

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

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

 

當前名稱：mysql主從復制實驗環(huán)境測試
文章分享：http://www.chinadenli.net/article14/iphide.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供App設計、網(wǎng)站改版、企業(yè)網(wǎng)站制作、網(wǎng)頁設計公司、網(wǎng)站收錄、搜索引擎優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)