1. 選擇兩臺機器(這里選的centos6.5 final)，安裝相同版本的mysql

yum installmysql ;yum install mysql-server;

2. 啟動mysql

service mysqld start

3. 登錄兩個mysql，執行如下命令

GRANT REPLICATION SLAVE,REPLICATION CLIENT on *.* to repl@'mysql機器IP' identified by 'password';

復制用戶并授權

4. 配置主mysql的/etc/my.cnf

[client]

port = 3306

socket = /dev/shm/mysql/mysql.sock

default-character-set = utf8

[mysqld_safe]

socket = /dev/shm/mysql/mysql.sock

nice = 0

[mysqld]

user = mysql

socket = /dev/shm/mysql/mysql.sock

port = 3306

basedir = /usr

datadir = /mysql/data

log-bin = mysql-bin

tmpdir = /tmp

skip-external-locking

bind-address = 172.16.1.1

key_buffer = 16M

max_allowed_packet = 16M

thread_stack = 192K

thread_cache_size = 8

myisam-recover = BACKUP

query_cache_limit = 1M

query_cache_size = 16M

log_error = /mysql/log/mysqld.log

expire_logs_days = 10

max_binlog_size = 100M

log_bin = mysql-bin

binlog_format = ROW

server_id = 1

innodb_flush_log_at_trx_commit=1

innodb_support_xa = 1

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

[mysql]

[isamchk]

key_buffer = 16M

!includedir /etc/mysql/conf.d/

5. 配置備的mysql

[client]

port = 3306

socket = /dev/shm/mysql/mysql.sock

default-character-set = utf8

[mysqld_safe]

socket = /dev/shm/mysql/mysql.sock

nice = 0

[mysqld]

user = mysql

socket = /dev/shm/mysql/mysql.sock

port = 3306

basedir = /usr

datadir = /mysql/data

log-bin = mysql-bin

tmpdir = /tmp

skip-external-locking

bind-address = 172.16.1.2

key_buffer = 16M

max_allowed_packet = 16M

thread_stack = 192K

thread_cache_size = 8

myisam-recover = BACKUP

query_cache_limit = 1M

query_cache_size = 16M

log_error = /mysql/log/mysqld.log

expire_logs_days = 10

max_binlog_size = 100M

log_bin = mysql-bin

binlog_format = ROW

server_id = 2 # id與主的不同

relay_log = mysql-relay-bin

log_slave_updates = 1

read_only = 1 # slave是read only

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

[mysql]

[isamchk]

key_buffer = 16M

!includedir /etc/mysql/conf.d/

6. 重啟兩個mysql

7. 登錄主mysql，執行

show master status\G;

驗證正確性

8. 登錄備mysql，執行

CHANGE MASTER TO

MASTER_HOST='172.16.1.1',

MASTER_USER='repl',

MASTER_PASSWORD='password',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=106;

然后執行

start slave;

show slave status\G;

驗證正確性

執行

mysql -urepl -h172.16.1.1 -ppassword

測試備mysql是否能連接到主庫

9. 主備切換

修改my.cnf文件

read-only=1(主)

#read-only=1(備)

在備的mysql上執行

STOP SLAVE IO_THREAD;

SHOW PROCESSLIST;

再執行

STOP SLAVE;

RESET MASTER;

RESET SLAVE;

show master status \G；

在主的mysql上執行

RESET MASTER;

RESET SLAVE;

CHANGE MASTER TO

MASTER_HOST='172.16.1.2',

MASTER_USER='repl',

MASTER_PASSWORD='password',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=106;

start slave

10. 先重啟新的主mysql， 在重啟備mysql

service mysqld restart

參考：?http://blog.csdn.net/liuzhoulong/article/details/48289115

總結

以上是生活随笔為你收集整理的mysql连接主备_mysql主备配置方法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。