mysql主主同步

双主节点
node1:192.168.1.53
node2:192.168.1.54

主主同步
#yum install gcc gcc-c++ bison ncurses ncurses-devel
#useradd mysql -s /sbin/nologin
#tar -zxvf cmake-2.8.4.tar.gz
#./configure
#gmake && make install
#tar -zxvf mysql-5.5.14.tar.gz
#cmake ./
#make && make install

#mkdir -p /data/mysql/data
#mkdir -p /data/mysql/log/binlog
#chown mysql:mysql /data/mysql/ -R
#chown mysql:mysql /usr/local/mysql/ -R

#vim /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
拷贝启动脚本
#cp mysql.server /etc/init.d/mysqld
#chmod +x /etc/init.d/mysqld
拷贝配置文件
#cp my-medium.cnf /etc/my.cnf 
#vim /etc/my.cnf  
配置文件大体内容,根据自己实际情况来改
[client]
port       = 3306
socket       =/tmp/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/data
log-error = /data/mysql/log/mysql_error.log
pid-file = /data/mysql/mysql.pid

port       = 3306
socket       =/tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

slow_query_log = ON
long_query_time = 2
slow_query_log_file = /data/mysql/log/slow.log
log-bin=/data/mysql/log/binlog/mysql-binlog
binlog_format=mixed
relay_log=/data/mysql/log/binlog/mysql-realy-binlog

server-id   =1               //一定要不一样
skip-slave-start
replicate-wild-ignore-table=mysql.%

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

初始化数据库:
#./mysql_install_db --user=mysql --datadir=/data/mysql/data--basedir=/usr/local/mysql
启动数据库
#/etc/init.d/mysqld start
设置数据库root密码
#/usr/local/mysql/bin/mysqladmin -u root password 123456


#mysql -u root -p 123456

node1:192.168.1.53
mysql> grant replication slave on *.* toslaveuser@'192.168.1.54' identified by '123456';
mysql> flush privileges;
mysql> show master status;
记住这里的File 和Position

node2:192.168.1.54
mysql> change master to MASTER_HOST='192.168.1.53',MASTER_USER='slaveuser', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-binlog.000008', MASTER_LOG_POS=107;

//这里的mysql-binlog.000008和107分别为刚才的file 和Position的数据
查看是否同步成功:
mysql> show slave status\G;
这两项是yes,说明连接成功.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes



因为要主主同步,所以还要将node1于node2同步,方法同上
node2:192.168.1.54
mysql> grant replication slave on *.* toslaveuser@'192.168.1.53' identified by '123456';
mysql>show master status;

node1:192.168.1.53
mysql> change master to MASTER_HOST='192.168.1.54',MASTER_USER='slaveuser', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-binlog.000002', MASTER_LOG_POS=476;
查看是否连接陈功
mysql>show slave status\G;
因为在配置文件里设置了 skip-slave-start,所以每次重启数据库,需要手动开启同步
mysql>slave start;
同理关闭命令
>slave stop;