ubuntu安装mysql双主

环境

下载软件及安装依赖

下载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