mysql 5.5双机热备份 master-master

时间：2022-09-23 14:15:13

mysql 5.5双机热备份 master-master

一，系统环境centos 6.3 ,master A IP:192.168.1.28,master B ip:192.168.1.29。二，安装cmake与mysql1，安装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/networkHOSTNAME=master1在另外一台修改成master2# cd /usr/local/mysqlscripts/mysql_install_db --user=mysql --datadir=/data/mysql/data 三，下面是masterA服务器的配置文件：[client]default-character-set = utf8port=3306socket = /tmp/mysql.sock[mysqld]user = mysqlport=3306socket = /tmp/mysql.sockbasedir = /usr/local/mysqldatadir=/data/mysql/datapid-file = /data/mysql/mysql.pidlog-error = /data/mysql/mysql-error.log#max_connections=1000#log_slave_update =1log-bin = /data/mysql/binlog/mysql-binlog-bin-index = /data/mysql/binlog/mysql-bin.indexbinlog_format = mixedbinlog_cache_size = 4Mmax_binlog_cache_size = 8Mmax_binlog_size = 1G

expire_logs_days = 30

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

binlog-do-db=smallbinlog-ignore-db=mysqlreplicate-do-db=small

replicate-ignore-db=mysql

#同步参数:

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

log-slave-updatesslave-skip-errors=all

sync_binlog=1

auto_increment_offset=1

auto_increment_increment=2key_buffer_size = 384Msort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size =16Mjoin_buffer_size =2Mthread_cache_size = 300query_cache_limit = 2Mquery_cache_min_res_unit =2Kthread_concurrency = 8table_cache =614table_open_cache = 512open_files_limit = 10240back_log = 600max_connections = 5000max_connect_errors = 6000external-locking = FALSEmax_allowed_packet =16Mdefault_storage_engine = MyISAM#default_storage_engine = InnoDBthread_stack =192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 256Mmax_heap_table_size =512Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 64Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverlong_query_time = 2slow_query_log = onslow_query_log_file = /data/mysql/slow.loglog-queries-not-using-indexes =onlog-slow-admin-statementsskip-name-resolveskip-external_lockinglog_bin_trust_function_creators=1#skip-networking#skip-innodbinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 512Minnodb_data_file_path = ibdata1:256M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit =2innodb_log_file_size =128Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout =240innodb_file_per_table = 0innodb_status_file = 1interactive_timeout=120wait_timeout=120server-id=1#innodb_flush_logs_at_trx_commit=1[mysqldump]quickmax_allowed_packet = 64M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M四，master-B的配置文件[client]default-character-set = utf8port=3306socket = /tmp/mysql.sock[mysqld]user = mysqlport=3306socket = /tmp/mysql.sockbasedir = /usr/local/mysqldatadir=/data/mysql/datapid-file = /data/mysql/mysql.pidlog-error = /data/mysql/mysql-error.log#max_connections=1000#log_slave_update =1log-bin = /data/mysql/binlog/mysql-binlog-bin-index = /data/mysql/binlog/mysql-bin.indexbinlog_format = mixedbinlog_cache_size = 4Mmax_binlog_cache_size = 8Mmax_binlog_size = 1G

expire_logs_days = 30

#需要同步的数据库

binlog-do-db=smallbinlog-ignore-db=mysqlreplicate-do-db=smallreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allsync_binlog=1auto_increment_increment=2auto_increment_offset=2key_buffer_size = 384Msort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size =16Mjoin_buffer_size =2Mthread_cache_size = 300query_cache_limit = 2Mquery_cache_min_res_unit =2Kthread_concurrency = 8table_cache =614table_open_cache = 512open_files_limit = 10240back_log = 600max_connections = 5000max_connect_errors = 6000external-locking = FALSEmax_allowed_packet =16Mdefault_storage_engine = MyISAM#default_storage_engine = InnoDBthread_stack =192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 256Mmax_heap_table_size =512Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 64Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverlong_query_time = 2slow_query_log = onslow_query_log_file = /data/mysql/slow.loglog-queries-not-using-indexes =onlog-slow-admin-statementsskip-name-resolveskip-external_lockinglog_bin_trust_function_creators=1#skip-networking#skip-innodbinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 512Minnodb_data_file_path = ibdata1:256M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit =2innodb_log_file_size =128Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout =240innodb_file_per_table = 0innodb_status_file = 1interactive_timeout=120wait_timeout=120server-id=2[mysqldump]quickmax_allowed_packet = 64M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_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: YesMaster B:Mysql>show slave status \G Slave_IO_Running: Yes 这两个为yes表示正常 Slave_SQL_Running: Yes


本文出自 “damon” 博客，请务必保留此出处http://damondeng.blog.51cto.com/1038075/1151900

标签：

相关文章