mysql连接主备_mysql主备配置方法

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