MySQL主从数据库配置

【MySQL主从数据库配置 -- master】
[mysqld]
datadir=/real/mysqldb/commentmaster/var
socket=/real/mysqldb/commentmaster/mysql.sock

port=7001
server-id = 7001
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=test
#binlog_do_db=comment
#binlog_do_db=mdbcomment
log-bin=master-mysql-bin

log-warnings

skip-locking
back_log = 200
key_buffer = 256M
max_allowed_packet = 2M
table_cache = 10000
sort_buffer_size = 8M
net_buffer_length = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 32M
thread_cache_size = 8
query_cache_size = 32M
max_connections = 2000
max_connect_errors = 200
connect_timeout = 1000
interactive_timeout = 30
wait_timeout = 30

default_character_set = gbk

[mysqld_safe]
log-error=/real/mysqldb/commentmaster/mysql.log
pid-file=/real/mysqldb/commentmaster/var/mysql.pid

【给主数据库授予一个可以进行复制的用户】
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.33.49' IDENTIFIED BY 'slavepass';

【MySQL主从数据库配置 -- 即是主又是从 master_slave】
[mysqld]
datadir=/real/mysqldb/commentmasterslave/var
socket=/real/mysqldb/commentmasterslave/mysql.sock

port=7002
server-id = 7002
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=test
relay-log = masterslave-relay-bin
log-bin=masterslave-mysql-bin
log-slave-updates

master-host=192.168.0.48
master-port=7001
master-user=repl
master-password=slavepass
master-connect-retry=60

log-warnings

skip-locking
back_log = 200
key_buffer = 256M
max_allowed_packet = 2M
table_cache = 10000
sort_buffer_size = 8M
net_buffer_length = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 32M
thread_cache_size = 8
query_cache_size = 32M
max_connections = 2000
max_connect_errors = 200
connect_timeout = 1000
interactive_timeout = 30
wait_timeout = 30

default_character_set = gbk

[mysqld_safe]
log-error=/real/mysqldb/commentmasterslave/mysql.log
pid-file=/real/mysqldb/commentmasterslave/var/mysql.pid


【MySQL主从数据库配置 -- slave】
[mysqld]
datadir=/real/mysqldb/commentslave/var
socket=/real/mysqldb/commentslave/mysql.sock

port=7003
server-id = 7003
relay-log = slave-relay-bin
#replicate-do-db=comment
#replicate-do-db=mdbcomment
#log-bin=mysql-bin

master-host=192.168.0.49
master-port=7002
master-user=repl
master-password=slavepass
master-connect-retry=60

log-warnings

#skip-slave-start
skip-locking
back_log = 200
key_buffer = 256M
max_allowed_packet = 2M
table_cache = 10000
sort_buffer_size = 8M
net_buffer_length = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 32M
thread_cache_size = 8
query_cache_size = 32M
max_connections = 2000
max_connect_errors = 200
connect_timeout = 1000
interactive_timeout = 30
wait_timeout = 30

default_character_set = gbk

[mysqld_safe]
log-error=/real/mysqldb/commentslave/mysql.log
pid-file=/real/mysqldb/commentslave/var/mysql.pid


==================================
注意：commentmaster、commentmasterslave、commentslave需要 chown mysql.mysql

注意：读写分离，slave的mysql用户只需要有读权限，防止用户写入数据，否则会出现同步问题。
grant select on comment.* to comment@'192.168.0.%' identified by 'password'

注意：由于设置了slave的配置信息，mysql在数据库目录下生成master.info 和 relay-log.info
所以如有要修改相关slave的配置要先删除这两个文件，否则修改的配置不能生效。

如果 slave 同步出现问题：
mysql > stop slave
mysql > CHANGE MASTER TO MASTER_HOST='192.168.33.48', MASTER_PORT=7001, MASTER_USER='msuser', MASTER_PASSWORD='MSuser77', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;
mysql > start slave

如果解决不了，彻底删除从库，从新创建从库
1、删除master.info、relay-log.info
2、删除所有masterslave-relay-bin
3、复制主库的数据库，拷贝到从库
4、启动从库

==================================

相关的配置参数意义已做了说明，主要就是多了配置主数据库服务器上的复制账号的信息。然后我们启动从数据库服务器，注意启动从数据库服务器后，并没有启动复制线程，我们需要在命令行中执行如下命令来启动复制功能：
slave start
启动后，我们就可以通过如下命令来查看复制的状态了：
show slave status;
然后我们就可以看到系统的输出，第一个就是Slave_IO_State，它的值通常就是Waiting for master to send event，然后我们也还可以看到我们刚才配置的主数据库服务器的IP地址、复制账号等信息。
我们还可以在从数据库服务器上动态的改变对主数据库的配置信息，通过如下命令来进行：
CHANGE MASTER TO MASTER_HOST=’主数据库服务器的IP地址’, MASTER_PORT=3306,MASTER_USER=’主数据库上的复制帐号’, MASTER_PASSWORD=’密码’;


如果从数据库服务器在同步的过程中出现了问题，那么我们可以通过reset slave来重置从数据库服务器的复制线程，从数据库服务器上的通常操作命令有：
Slave start; --启动复制线程
Slave stop; --停止复制线程
Reset slave; --重置复制线程
Show slave status; --显示复制线程的状态
Change master to; --动态改变到主数据库的配置


==================================

#!/bin/sh
/real/mysqldb/commentmaster/bin/mysql -S /real/mysqldb/commentmaster/mysql.sock

#!/bin/sh
/real/mysqldb/commentmaster/bin/mysqld_safe --defaults-file=/real/mysqldb/commentmaster/my.cnf &

#!/bin/sh
/real/mysqldb/commentmaster/bin/mysqladmin shutdown -S /real/mysqldb/commentmaster/mysql.sock