MySQL数据库在云主机上搭建主从配置

Mysql主从方案介绍mysql主从方案主要作用:读写分离,使数据库能支撑更大的并发了的同时也支付了数据安全性了，如果有数据丢失从服务器会是最新的备份数据了，这也是各大网站常用的一种数据实时备份的方法，下文来简单的介绍mysql主从配置吧。

1,已经事先购买了两台云主机

1. 10.4.14.168 101.69.178.208[联通]Master
3. 10.4.5.9 101.69.178.219[联通]Slave

2,分别安装MySQL,代码如下:

1. #yuminstallmysql-server–enablerepo=remi
3. #yuminstallmysql-devel

3,查看MySQL安装情况,代码如下:

1. [root@10-4-14-168~]#rpm-qa|grepmysql*
3. mysql-5.1.73-3.el6_5.x86_64
4. mysql-libs-5.1.73-3.el6_5.x86_64
5. mysql-server-5.1.73-3.el6_5.x86_64
6. mysql-devel-5.1.73-3.el6_5.x86_64

4,启动MySQL,代码如下:

service mysqld start

5,登陆MySQL并且修改密码并且删除空用户,代码如下:

1. mysql>UPDATEmysql.userSETpassword=PASSWORD('ge0513.mysql')
2. ->WHEREuser='root';
3. QueryOK,3rowsaffected(0.00sec)
4. Rowsmatched:3Changed:3Warnings:0
5. mysql>SELECTuser,host,passwordFROMmysql.user;
6. +------+-------------+-------------------------------------------+
7. |user|host|password|
8. +------+-------------+-------------------------------------------+
9. |root|localhost|*7AE39BE5035D5C32361400FF7DEDD757AA76896A|
10. |root|10-4-14-168|*7AE39BE5035D5C32361400FF7DEDD757AA76896A|
11. |root|127.0.0.1|*7AE39BE5035D5C32361400FF7DEDD757AA76896A|
12. ||localhost||
13. ||10-4-14-168||
14. +------+-------------+-------------------------------------------+
15. 5rowsinset(0.00sec)
16. mysql>DROPuser''@localhost;
17. QueryOK,0rowsaffected(0.00sec)
18. mysql>DROPuser''@'10-4-14-168';
19. QueryOK,0rowsaffected(0.00sec)

6 需改配置文件,代码如下:

1. bind-address=10.6.20.1
2. port=3306
3. character_set_server=utf8
4. max_connect_errors=1000000
5. max_connections=500
6. datadir=/data/mysql
7. log-error=/var/log/mysql/mysql-error.log-error
8. skip_name_resolve
9. back_log=2000
10. log-bin=/data/mysql
11. binlog-format=MIXED
12. expire_logs_days=2

7,主库上创建复制用户,代码如下:

GRANT REPLICATION SLAVE ON *.* TO gechong@10.4.14.168 IDENTIFIED BY 'gechong';

8,在主库上执行,代码如下:

1. mysql>showmasterstatus;
2. +---------------+----------+--------------+------------------+
3. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
4. +---------------+----------+--------------+------------------+
5. |binlog.000003|412|||
6. +---------------+----------+--------------+------------------+
7. 1rowinset(0.00sec)

9,在从库的配置文件中添加之后重启服务,代码如下:

1. master-host=10.4.14.168
2. master-user=gechong
3. master-password=gechong
4. binlog-do-db=test

10,在从库上执行,代码如下:

1. mysql>startslave;
2. QueryOK,0rowsaffected,1warning(0.00sec)
3. --phpfensi.com

11,尝试在主库上添加新表,看是否从库上也会出现.