mysql 5.5 双机_mysql 5.5双机热备份 master-master

一，系统环境centos 6.3 ,master A IP:192.168.1.28,master B ip:192.168.1.29。

二，安装cmake与mysql

1，安装cmake

#tar xf cmake-2.8.8.tar.gz

#cd cmake-2.8.8

# ./bootstrap

# gmake && gmake install

2，安装mysql

#useradd -r mysql -s /sbin/nologin

#mkdir -p /data/mysql/{data,binlog,relaylog}

#chown mysql:mysql -R /data/mysql

# tar xf mysql-5.5.27.tar.gz

#cd mysql-5.5.27

#cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/data/mysql/data -DWITH_DEBUG=0 -DSYSCONFDIR=/etc -DMYSQL_TCP_PORT=3306

#会编译不过去，提示需要安装ncurses-devel,yum -y install ncurses-devel

#rm -f CMakeCache.txe 重新执行cmake

# make && make install

#cd /usr/local/mysql

#chown -R mysql:mysql *

# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

#chmod +x /etc/init.d/mysqld

#chkconfig -add mysqld

#chkconfig mysqld on

# vim /etc/profile 添加PATH=$PATH:/usr/local/mysql/bin

# . /etc/profile(或者export PATH=$PATH:/usr/local/mysql/bin)

# ln -sv /usr/local/mysql/include/ /usr/include/mysql

# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf 加载库文件

# ldconfig -v |grep mysql

# vim /etc/man.config 添加一行MANPATH /usr/local/mysql/man

将主服务器克隆一份，并修改IP与MAC地址，保证两台机器能够互相ping通。

环境已经基本Ok

先修改好两台服务器的主机名，同时修改好,同时关闭selinux。完了之后最好重启

#vim /etc/sysconfig/network

HOSTNAME=master1在另外一台修改成master2

# cd /usr/local/mysql

scripts/mysql_install_db --user=mysql --datadir=/data/mysql/data

三，下面是masterA服务器的配置文件：

[client]

default-character-set = utf8

port=3306

socket = /tmp/mysql.sock

[mysqld]

user = mysql

port=3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir=/data/mysql/data

pid-file = /data/mysql/mysql.pid

log-error = /data/mysql/mysql-error.log

#max_connections=1000

#log_slave_update =1

log-bin = /data/mysql/binlog/mysql-bin

log-bin-index = /data/mysql/binlog/mysql-bin.index

binlog_format = mixed

binlog_cache_size = 4M

max_binlog_cache_size = 8M

max_binlog_size = 1G

expire_logs_days = 30

#不需要同步的数据，且不记录到binlog中。

binlog-do-db=small

binlog-ignore-db=mysql

replicate-do-db=small

replicate-ignore-db=mysql

#同步参数:

#保证slave挂在任何一台master上都会接收到另一个master的写入信息

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_offset=1

auto_increment_increment=2

key_buffer_size = 384M

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size =16M

join_buffer_size =2M

thread_cache_size = 300

query_cache_limit = 2M

query_cache_min_res_unit =2K

thread_concurrency = 8

table_cache =614

table_open_cache = 512

open_files_limit = 10240

back_log = 600

max_connections = 5000

max_connect_errors = 6000

external-locking = FALSE

max_allowed_packet =16M

default_storage_engine = MyISAM

#default_storage_engine = InnoDB

thread_stack =192K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 256M

max_heap_table_size =512M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

long_query_time = 2

slow_query_log = on

slow_query_log_file = /data/mysql/slow.log

log-queries-not-using-indexes =on

log-slow-admin-statements

skip-name-resolve

skip-external_locking

log_bin_trust_function_creators=1

#skip-networking

#skip-innodb

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 512M

innodb_data_file_path = ibdata1:256M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit =2

innodb_log_file_size =128M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout =240

innodb_file_per_table = 0

innodb_status_file = 1

interactive_timeout=120

wait_timeout=120

server-id=1

#innodb_flush_logs_at_trx_commit=1

[mysqldump]

quick

max_allowed_packet = 64M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

四，master-B的配置文件

[client]

default-character-set = utf8

port=3306

socket = /tmp/mysql.sock

[mysqld]

user = mysql

port=3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir=/data/mysql/data

pid-file = /data/mysql/mysql.pid

log-error = /data/mysql/mysql-error.log

#max_connections=1000

#log_slave_update =1

log-bin = /data/mysql/binlog/mysql-bin

log-bin-index = /data/mysql/binlog/mysql-bin.index

binlog_format = mixed

binlog_cache_size = 4M

max_binlog_cache_size = 8M

max_binlog_size = 1G

expire_logs_days = 30

#需要同步的数据库

binlog-do-db=small

binlog-ignore-db=mysql

replicate-do-db=small

replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=2

key_buffer_size = 384M

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size =16M

join_buffer_size =2M

thread_cache_size = 300

query_cache_limit = 2M

query_cache_min_res_unit =2K

thread_concurrency = 8

table_cache =614

table_open_cache = 512

open_files_limit = 10240

back_log = 600

max_connections = 5000

max_connect_errors = 6000

external-locking = FALSE

max_allowed_packet =16M

default_storage_engine = MyISAM

#default_storage_engine = InnoDB

thread_stack =192K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 256M

max_heap_table_size =512M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

long_query_time = 2

slow_query_log = on

slow_query_log_file = /data/mysql/slow.log

log-queries-not-using-indexes =on

log-slow-admin-statements

skip-name-resolve

skip-external_locking

log_bin_trust_function_creators=1

#skip-networking

#skip-innodb

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 512M

innodb_data_file_path = ibdata1:256M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit =2

innodb_log_file_size =128M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout =240

innodb_file_per_table = 0

innodb_status_file = 1

interactive_timeout=120

wait_timeout=120

server-id=2

[mysqldump]

quick

max_allowed_packet = 64M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

五，创建授权用户

masterA:

Mysql>grant replication slave on *.* to rpuser1@192.168.1.29 identified by ‘123456’;

Mysql> flush privileges;

masterB:

Mysql>grant replication slave on *.* to rpuser2@192.168.1.28 identified by ‘123456’;

Mysql> flush privileges;

六，准备复制

Master A：

mysql> flush tables with read lock\G

Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000003

Position: 107

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

Mysql> unlock tables;

master B：

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000004

Position: 107

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

Mysql> unlock tables;

在各自机器上执行CHANGE MASTER TO命令。

Master A：

mysql> change master to

-> master_host='192.168.1.28',

-> master_user='rpuser2',

-> master_password='123456',

-> master_log_file='mysql-bin.000004',

-> master_log_pos=107;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

master B：

mysql> change master to

-> master_host='192.168.1.29',

-> master_user='rpuser1',

-> master_password='123456',

-> master_log_file='mysql-bin.000003',

-> master_log_pos=107;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

七，查看与验证

Master A:

Mysql>show slave status \G

Slave_IO_Running: Yes 这两个为yes表示正常

Slave_SQL_Running: Yes

Master B:

Mysql>show slave status \G

Slave_IO_Running: Yes 这两个为yes表示正常

Slave_SQL_Running: Yes