MySQL數據庫在雲主機上搭建主從配置

Mysql主從方案介紹mysql主從方案主要作用:讀寫分離,使數據庫能支撐更大的並發了的同時也支付了數據安全性了，如果有數據丟失從服務器會是最新的備份數據了，這也是各大網站常用的一種數據實時備份的方法，下文來簡單的介紹mysql主從配置吧。

1：已經事先購買了兩台雲主機

10.4.14.168      101.69.178.208 [聯通] Master

10.4.5.9         101.69.178.219 [聯通] Slave

2：分別安裝MySQL

 代碼如下 復制代碼

#yum install mysql-server –enablerepo=remi

#yum install mysql-devel

 

3：查看MySQL安裝情況

 代碼如下 復制代碼

[root@10-4-14-168 ~]#  rpm -qa | grep mysql*

mysql-5.1.73-3.el6_5.x86_64
mysql-libs-5.1.73-3.el6_5.x86_64
mysql-server-5.1.73-3.el6_5.x86_64
mysql-devel-5.1.73-3.el6_5.x86_64

4：啟動MySQL

 代碼如下 復制代碼 service mysqld start

 

5：登陸MySQL並且修改密碼並且刪除空用戶

 代碼如下 復制代碼 mysql> UPDATE mysql.user SET password = PASSWORD('ge0513.mysql')
    -> WHERE user = 'root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
mysql> SELECT user,host,password FROM mysql.user;
+------+-------------+-------------------------------------------+
| user | host        | password                                  |
+------+-------------+-------------------------------------------+
| root | localhost   | *7AE39BE5035D5C32361400FF7DEDD757AA76896A |
| root | 10-4-14-168 | *7AE39BE5035D5C32361400FF7DEDD757AA76896A |
| root | 127.0.0.1   | *7AE39BE5035D5C32361400FF7DEDD757AA76896A |
|      | localhost   |                                           |
|      | 10-4-14-168 |                                           |
+------+-------------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql> DROP user ''@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP user ''@'10-4-14-168';
Query OK, 0 rows affected (0.00 sec)

 

6 需改配置文件

 代碼如下 復制代碼 bind-address = 10.6.20.1
port = 3306
character_set_server = utf8
max_connect_errors = 1000000
max_connections = 500
datadir = /data/mysql
log-error = /var/log/mysql/mysql-error.log-error
skip_name_resolve
back_log = 2000
log-bin = /data/mysql
binlog-format = MIXED
expire_logs_days = 2

 

7：主庫上創建復制用戶

 代碼如下 復制代碼

GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY 'gechong';

8：在主庫上執行

 代碼如下 復制代碼

mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 |      412 |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

9：在從庫的配置文件中添加之後重啟服務

 代碼如下 復制代碼 master-host = 10.4.14.168
master-user = gechong
master-password = gechong
binlog-do-db = test

 

10：在從庫上執行

 代碼如下 復制代碼

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

11：嘗試在主庫上添加新表，看是否從庫上也會出現