ubuntu安装mysql双主
环境
|
主机名 |
Ip地址 |
|
DBServerA |
10.0.1.231 |
|
DBServerB |
10.0.1.232 |
下载软件及安装依赖
下载mysql5.7
http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.14.tar.gz
下载boost_1_59_0：
http://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz
#安装依赖组件 :
apt-get install lrzsz make cmake g++ git vim gcc libncurses5-dev bison
安装
#创建数据文件：
mkdir /data/mysqldata && chown mysql.mysql /data/mysqldata
#编译参数
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysqldata -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_BOOST=/data/mysql/boost_1_59_0 -DMYSQL_TCP_PORT=3306
#编译安装
make -j4
make install
#cp配置文件
cp support-files/my-default.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
初始化配置安装：
cd /usr/local/mysql/bin
./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldata
mkdir /data/mysqldata/log && chown -R mysql:mysql /data/mysqldata/log
#配置配置文件
[client]
port = 3306
socket = /data/mysqldata/mysql.sock
[mysqld]
binlog_format = mixed
port = 3306
socket = /data/mysqldata/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysqldata
#master-to-master
log-bin = mysql1-bin
server-id = 1
#expire-logs-days = 30 #only 30 days binlog backup
#replicate-do-db = test
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = sys
binlog-ignore-db = performance_schema
auto-increment-increment = 5
auto-increment-offset = 1
log-slave-updates = ON
max_connections=3000
log_timestamps=SYSTEM
#============================================
back_log=50
max_user_connections=2000
innodb_thread_concurrency=8
default-storage-engine=InnoDB
innodb_buffer_pool_size=8192M
innodb_log_buffer_size=8M
innodb_log_file_size=48M
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=50
long_query_time = 1
slow_query_log=YES
slow_query_log_file=/data/mysqldata/log/slow.log
log-error=/data/mysqldata/log/error.log
DBServerB
[client]
port = 3306
socket = /data/mysqldata/mysql.sock
[mysqld]
binlog_format = mixed
port = 3306
socket = /data/mysqldata/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysqldata
#master-to-master
log-bin = mysql2-bin
server-id = 2
#expire-logs-days = 30 #only 30 days binlog backup
#replicate-do-db = test
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = sys
binlog-ignore-db = performance_schema
auto-increment-increment = 5
auto-increment-offset = 2
log-slave-updates = ON
max_connections=3000
log_timestamps=SYSTEM
back_log=50
max_user_connections=2000
innodb_thread_concurrency=8
default-storage-engine=InnoDB
innodb_buffer_pool_size=8192M
innodb_log_buffer_size=8M
innodb_log_file_size=48M
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=50
long_query_time = 1
slow_query_log=ON
slow_query_log_file=/data/mysqldata/log/slow.log
log-error=/data/mysqldata/log/error.log
#登陆mysql
cat ~/.mysql_secret
,2jbl!yd0rC#
mysql -uroot -p",2jbl!yd0rC#"
修改root密码
alter user 'root'@'localhost' identified by 'root';
flush privileges;
配置主从用户
DBServerA
GRANT REPLICATION SLAVE ON *.* TO 'mysql2'@'10.0.1.232' IDENTIFIED BY 'mysql2';
DBServerB
GRANT REPLICATION SLAVE ON *.* TO 'mysql1'@'10.0.1.231' IDENTIFIED BY 'mysql1';
配置主从
DBServerA
CHANGE MASTER TO MASTER_HOST='10.0.1.232',MASTER_USER='mysql1',MASTER_PASSWORD='mysql1';
start slave;
DBServerB
CHANGE MASTER TO MASTER_HOST='10.0.1.231',MASTER_USER='mysql2',MASTER_PASSWORD='mysql2';
start slave;
DBServerA
change master to MASTER_HOST='10.0.1.232',MASTER_PORT=3306,MASTER_USER='mysql1',MASTER_PASSWORD='mysql1',MASTER_LOG_FILE='mysql2-bin.000002',MASTER_LOG_POS=809;
DBServerB
change master to MASTER_HOST='10.0.1.231',MASTER_PORT=3306,MASTER_USER='mysql2',MASTER_PASSWORD='mysql2',MASTER_LOG_FILE='mysql1-bin.000003',MASTER_LOG_POS=809;
问题：
一、从DBServer从主DBServer读取binlog产生问题时
mysql -u root -p
mysql> stop slave;
mysql> change master to
mysql> master_host='10.0.1.231',
mysql> master_user='mysql1',
mysql> master_password='mysql1',
mysql> master_log_file='',
mysql> master_log_pos= ;
然后再从DBServer上删除两个以relay××log为结尾的文件
2.启动从DBServer
mysql> slave start # 或者是start slave，启动从DBServer
mysql>show slave status #查看从DBServer状态
二、mysqlbinlog 操作：
show binlog events\G 查看binlog记录的位置和文件
根据pos位置恢复：
mysqlbinlog --start-position=3313 --stop-position=3557 mysql2-bin.000001 >test.sql 然后source
根据截止时间点恢复：
mysqlbinlog --stop-date="2016-03-03 17:41:28" /data/mysql1/mysql-bin.000002 > test.sql
