mysql 单服务器主从复制_单服务器MySQL主从复制实践

一、先配置好两个MySQL实例并可以正常启动

1. 首先在Linux上先安装好MySQL数据库，此处省略详细的安装步骤(可以使用yum安装)

yum -y install mysqld mysql

2. 在/usr/local目录下新建目录mysqls/mysql1, mysqls/mysql2 分别作为我们的主数据和从数据库目录，新建mysqls/run作为MySQL PID存放目录，新建mysqls/log作为MySQL日志目录

mkdir -p /usr/local/mysqls/mysql1 /usr/local/mysqls/mysql2

mkdir -p /usr/local/mysqls/run /usr/local/mysqls/log

3. 复制MySQL默认的配置文件/etc/my.cnf到/usr/local/mysqls下,并复制两份分别命名为mysql1.cnf和mysql2.cnf。这两个文件将作为主从MySQL数据库的配置文件(使用这种方式，可以不影响原有MySQL配置，默认MySQL仍可正常使用)

cp /etc/my.cnf /usr/local/mysqls/mysql1.cnf

chown -R mysql:mysql

cd /usr/local/mysqls

cp mysql1.cnf mysql2.cnf

4. 修改MySQL实例配置文件，指定datadir, socket, port, log-error, pid-file的路径：

mysql1.cnf 配置如下：

[mysqld]

datadir=/usr/local/mysqls/mysql1

socket=/usr/local/mysqls/mysql1/mysql.sock

port=33061

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

#default-character-set=utf8

character-set-server=utf8

[mysqld_safe]

log-error=/usr/local/mysqls/log/mysql1.log

pid-file=/usr/local/mysqls/run/mysql1.pid

[client]

default-character-set=utf8

socket=/usr/local/mysqls/mysql1/mysql.sock

mysql2.cnf 参考上面配置，使用mysql2目录，端口号设置为33062

5. 接下来使用mysql_install_db脚本初始化这两个MySQL数据库实例(该脚本会在mysql1和mysql2目录下自动安装MySQL基本数据库 : mysql)

/usr/bin/mysql_install_db --defaults-file=/usr/local/mysqls/mysql1.cnf

/usr/bin/mysql_install_db --defaults-file=/usr/local/mysqls/mysql2.cnf

6. 为了每次方便启动实例，我们可以编写如下启动脚本，脚本中使用mysqld_safe启动MySQL实例：

start_mysql1.sh 内容如下：

#/bin/bash

no=1

exe=/usr/bin/mysqld_safe

madmin=/usr/bin/mysqladmin

base=/usr/local/mysqls

sub=mysql$no

conf=$base/$sub.cnf

socket=$base/$sub/mysql.sock

pidfile=$base/run/$sub.pid

port=3306$no

timeout=60

$exe --defaults-file=$conf 2>&1 &

while [ $timeout -gt 0 ]; do

$madmin --socket="$socket" --user=root --port=$port ping >/dev/nul 2>&1

mret=$?

if [ $mret -eq 0 ]; then

now=`date +'%Y-%m-%d %H:%M:%S'`

echo "[$now] Start $sub : OK"

echo "[$now] PID : `cat $pidfile`"

netstat -atulnp | grep $port

break

fi

sleep 1

let timeout=${timeout}-1

done

start_mysql1.sh 用于启动mysql1，同样我们可以类似编写start_mysql2.sh用于启动mysql2(修改no变量的值为2即可)

注：shell 脚本文件在执行前需要设置可执行权限(类似这样：chown u+x start_mysql1.sh)

7. 至此，MySQL两个实例的准备工作就做好了，我们可以尝试启动它们了(以mysql1为例)

/usr/local/mysqls/start_mysql1.sh

如果一切顺利，将出现以下结果：

二、 接下来，我们开始设置主从复制

1. 停止MySQL实例，为了方便同样编写停止脚本

stop_mysql.sh内容如下：

#/bin/bash

if [ $# -ne 1 ]; then

echo "Usage : $0 "

exit -1

fi

no=$1

sub=mysql$no

base=/usr/local/mysqls

pidfile=$base/run/$sub.pid

if [ ! -f "$pidfile" ]; then

echo "'$pidfile' not exist, mysql server may not start"

exit -2

fi

pid=`cat $pidfile`

kill $pid

执行：stop_mysql.sh 1 即可停止mysql1, 后面参数改为2即可停止mysql2，就像下面这样：

2. 配置主数据库，开启复制功能(这里使用mysql1作为主数据库,mysql2作为从数据库)

mysql1.cnf中[mysqld]标记下面增加如下配置：

[mysqld]

...

#replication

log-bin=/usr/local/mysqls/log/mysql1-bin.log    # 这是MySQL bin log的位置

server-id=1    # server id, 标识mysql

# 下面两个参数表示确保InnoDB事务数据库可以保持最大的持久性和一致性

innodb_flush_log_at_trx_commit=1

sync_binlog=1

# 额外的参数，我们可以设置复制哪个库、哪个表，不复制哪个库、哪个表(这里不详述，具体参考MySQL官方文档)

# 例如：

# replicate-do-db=mydb

# replicate-do-table=mytable

# replicate-ignore-db=mysql

# replicate-ignore-table=user

3. 启动mysql1,mysql命令连接并添加复制账户，同时查看master状态

start_mysql1.sh

mysql -S /usr/local/msyqls/mysql1.sock -uroot -p

mysql> grant all on *.* to'rep'@'localhost'identified by 'replpass'; # 实际环境中这里根据自己需求而定

mysql> flush privileges;

mysql> show master status \G

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

File: mysql1-bin.000002

Position: 1486

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

4. 如果主从数据库是全新搭建的(没有实际数据)可以不用备份主库到从库，否则需要导出主库数据到从库

# 设置读锁(禁止写入)

mysql> flush tables with read lock;

# 执行备份操作

/usr/bin/mysqldump -uroot -p --database mydb1, mydb2 ... > dump.sql

#解锁

mysql> unlock tables;

‍

5. 配置从数据库复制参数

mysql2.cnf中mysqld标记下增加如下配置：

#replication

log-bin=/usr/local/mysqls/log/mysql2-bin.log

server-id=2

master-host=localhost    # 主库地址

master-port=33061        # 主库端口号

master-user=rep          # 上面所设置的复制账户

master-pass=replpass     # 复制账户密码

master-connect-retry=60  # 重试连接的间隔时间

6. 启动从数据库，若主库有数据需要导入则先导入刚才的备份，接着设置复制起始位置，启动slave

start_mysql2.sh

mysql -S /usr/local/mysqls/mysql2/mysql.sock -uroot -p 

mysql -S /usr/local/mysqls/mysql2/mysql.sock -uroot -p

mysql> change master to

-> master_log_file=mysql1-bin.000002

-> master_log_pos=1486;

mysql> start slave;

mysql> show slave status \G

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

Slave_IO_State: Waiting for master to send event

Master_Host: localhost

Master_User: rep

Master_Port: 33061

Connect_Retry: 60

Master_Log_File: mysql1-bin.000002

Read_Master_Log_Pos: 1486

Relay_Log_File: mysql2-relay-bin.000002

Relay_Log_Pos: 252

Relay_Master_Log_File: mysql1-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 106

Relay_Log_Space: 554

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

7. OK , MySQL两实例主从复制就搭建好了。

注：文中使用mysql命令时指定socket文件是必须的，亦可以类似写相应的脚本以免每次连接都需加这个参数

mysql1.sh 内容如下：

#/bin/bash

no=1

base=/usr/local/mysqls/

sub=mysql$no

socket=$base/$sub/mysql.sock

mysql -S $socket $*

mysql2.sh 则修改no的值为2