Mysql Replication的安装和配置

--------------Mysql Install---------------------

#vi install_mysql.sh

groupadd mysql && useradd -g mysql mysql
tar zxvf mysql-5.0.33.tar.gz && cd mysql-5.0.33 && ./configure '--prefix=/usr/local/mysql' '--with-mysqld-ldflags=-all-static' '--with-mysqld-user=mysql' '--with-charset=cp932' '--with-pthread' 'cflags=-o3' 'cxxflags=-o3' 'cxx=gcc' && make && make install
cp support-files/my-medium.cnf /etc/my.cnf
cd /usr/local/mysql
bin/mysql_install_db --user=mysql
chown -r root  .
chown -r mysql var
chgrp -r mysql .
bin/mysqld_safe --user=mysql &

/usr/local/mysql/bin/mysqld_safe --user=mysql &

 

#chmod 755 install_mysql.sh

#./install_mysql.sh

 

-------------Mysql Replication Setup------------
# The MySQL server
[mysqld]
port            = 3306
server-id = 172
#log-bin
master-host = 10.99.1.171
master-user = slave
master-password = slave
master-port = 3306
slave-skip-errors = 1050,1007,1051,1062
read-only
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

 

在master执行：

mysql>GRANT FILE ON *.* TO IDENTIFIED BY 'slave';
mysql>GRANT REPLICATION SLAVE ON *.*  TO IDENTIFIED BY 'slave';

mysql>flush privileges;

 

修改slave的my.cnf：

master-host     =  10.99.1.181
master-user     =  slave
master-password =  slave
master-port     =  3306
server-id       =  182
slave-skip-errors = 1050,1007,1051,1062
read-only

master-host     =  10.99.1.181
master-user     =  slave
master-password =  slave
master-port     =  3306
server-id       =  183
slave-skip-errors = 1050,1007,1051,1062
read-only


补充：
1：MasterDB设置
2：MasterDB和Slave DB无法同步
3：增加一台Slave DB
4：同步失败
5：注意事项

1：MasterDB设置
1）my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
bind-address=192.168.131.164
socket          = /tmp/mysql.sock
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
max_connect_errors=100
max_connections = 2048
innodb_table_locks=0
server-id       =  164
log-bin

2）权限设置
GRANT REPLICATION SLAVE ON 'slave'@'xxx.xxx.xxx.xxx' TO IDENTIFIED BY 'your password';
FLUSH PRIVILEGES;

3）查看masterDB信息
mysql>show master status;


2：Master和Slave DB无法同步
1）检查mysql日志/usr/local/mysql5/var/{hostname}.err；
tail -n100 /user/local/mysql5/var/{hostname}.err

2）检查Slave DB的 /etc/my.cnf是否包含Master主机、端口、用户名、密码；
cat /etc/my.cnf

3）检查Master DB的 mysql.user表的权限设置；
select * from mysql.user;

4）如果已经修改了my.cnf，需要重新启动mysql daemon；
/user/local/mysql5/bin/mysqld_safe --sock=/tmp/mysql.sock &

5）Slave DB进入mysql command,执行 slave　start;show slave status;查看同步是否正常的标志是：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果其中一个不为Yes，同步失败，请看3.同步失败；

3：增加一台Slave DB
1）在已经安装好mysql（版本最好跟masterDB一致）的Slave DB上设置/etc/my.cnf；
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
bind-address=192.168.131.26
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
max_connect_errors=100
max_connections = 2048
innodb_table_locks=0
read-only
master-host     =  192.168.131.164
master-user     =  slave
master-password =  slave
master-port     =  3306
server-id       =  26
slave-skip-errors = 1050,1007,1051,1062

2）在masterDB上设置权限；
mysql> GRANT REPLICATION SLAVE ON *.*
    -> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

3）通过mysqldump导出masterDB的数据；
#/usr/local/mysql5/bin/mysqldump -hxxx -uxxx -pxxx --master-data --opt --databases {dbname} [{tablename}] /home/backupdb/dbname.sql
4）将导出数据导入SlaveDB；
#/usr/local/mysql5/bin/mysql -A -e “slave stop;”
#/usr/local/mysql/bin/mysql
5）在SlaveDB上启动同步进程；
#/usr/local/mysql/bin/mysql -A
msyql>slave start;

6）查看同步是否正常。
mysql>show slave status;


4：同步失败
I：mysql replication (Key重复)
原因：主从同步的时候，从服务器被写入了数据。
解决方法：跳过错误
执行指令：
    检查同步失败的错误号：#/usr/local/mysql/bin/mysql -A -e "show slave status\G" |grep Last_errno |awk -F: '{print $2}'
    如果为1062，执行：mysql>slave stop;
    mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER =1;
    mysql>slave start;
验证结果：
    mysql>show slave status;

    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

II：Access denied. You need the REPLICATION SLAVE privilege for this operation
原因：权限不够
解决方法：在masterDB上授权，并重新启动slave
mysql> GRANT REPLICATION SLAVE ON *.*
    -> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

III：#Slave: connected TO master '[e-mail]:3306',replication resumed IN log 'master-bin.003676' at position 444286437
#080603 20:53:10 [Note] Slave: received end packet FROM server, apparent master shutdown:
#080603 20:53:10 [Note] Slave I/O thread: Failed reading log event, reconnecting TO retry, log 'master-bin.003676' position 444292333
是由于多台slaveDB的server_id相同造成的。修改为不同值，
然后重启mysql daemon；执行:slave start;


5：注意事项
I：如果同步的master的db类型是执行存储过程的InnoDB，需要打开下面两项：
master my.cnf:
innodb_flush_log_at_trx_commit=1
sync_binlog=1
skip-networking=disable