单服务器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 <mysql no>"
        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 < dump.sql 
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