linux mysql 单机主从_Linux单机主从复制

# 单机主从复制

MySQL可以采用主从机制进行备份,如果一对一进行备份对于生成环境而言比较浪费资源,主服务器把数据变化记录到主日志,然后从服务器通过I/O线程读取主服务器的日志,并将它写入到从服务器的中继日志中,接着SQL线程线程读取中继日志,并且在从服务器上重放,从而实现MySQL复制.

MySQL具有可以运行多个实例的功能,这个功能是通过MySQLd_multi实现的.当一台机器上需要运行多个MySQL服务器时,MySQLd_multi是管理多个MySQLd的服务进程,这些服务进程程序用不同的unix socket或是监听不同的端口,通过命令,可以启动,关闭和报告所管理的服务器的状态.

# 单机主从复制准备

首先停止MySQL服务器

```shell

service mysql stop

```

把常用的工具添加到/usr/bin目录,命令如下

```shell

ln -s /usr/local/mysql/bin/mysqld_multi /usr/bin/mysqld_multi

ln -s /usr/local/mysql/scripts/mysql_install_db /usr/bin/mysql_install_db

```

初始化三个数据数据目录并安装3个MySQL服务,命令如下

```shell

cd /usr/local/mysql/

mkdir -p /usr/local/var/mysql1

mkdir -p /usr/local/var/mysql2

mkdir -p /usr/local/var/mysql3

./scripts/mysql_install_db --datadir=/usr/local/var/mysql1 --user=mysql

./scripts/mysql_install_db --datadir=/usr/local/var/mysql2 --user=mysql

./scripts/mysql_install_db --datadir=/usr/local/var/mysql3 --user=mysql

```

从MySQL的源码中把MySQLd_multi.server复制到/etc/init.d/目录下,命令执行如下

```shell

cd /usr/local/mysql/support-files/

cp ./mysqld_mysqld.server/etc/init.d/mysql_multi.server

```

配置数据库文件.直接配置/etc/my.conf,修改相应的属性

```ini

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

user = root

[mysqld1]

port = 3306

[mysqld2]

port = 3307

socket = /temp/mysql2.sock

datadir = /usr/local/var/mysql2

[mysqld3]

port = 3308

socket = /temp/mysql3.sock

datadir = /usr/local/var/mysql3

[mysqld]

```

使用mysqld_multi重启服务器

```shell

mysqld_multi --defults-extra-file=/etc/my.cnf stop

mysqld_multi --defults-extra-file=/etc/my.cnf start

```

查看数据状态

```shell

mysqld_multi --defults-extra-file=/etc/my.conf report

```

出现以下信息表示成功

```shell

Reporting MySQL servers

MySQL server from group: mysqld1 is running

MySQL server from group: mysqld2 is running

MySQL server from group: mysqld3 is running

```

查看端口状态

```shell

netstat -an|grep 330

```

# 实现单机主从复制

开启三个MySQL服务

```shell

mysqld_multi --defaults-extra-file=/etc/my.cnf start 1-3

netstat -an|grep 330

```

登录Master服务器,设置一个复制使用的账号,并赋予replication slave权限

```sql

grant replication slave on *.* to 'repl'@'127.0.0.1' identified by '123'

grant replication slave on *.* to 'repl'@'%' identified by '123';

```

修改Master 主数据库服务器的配置文件my.cnf,开启binlog,并设置server-id的值,需要重启服务器之后生效

```ini

[mysqld1]

port = 3306

log-bin = /usr/local/var/mysql1/mysql-bin

server-id = 1

```

在Master主服务器上,设置锁定有效,这个操作是为了确保没有数据库操作,以便获得一致性的快照

```shell

mysql -u root -P 3306 -S /tmp/mysql.sock

flush tables with read lock;

```

使用show master status命令查看日志情况,查询得到主服务器上当前的二进制名和偏移量值.这个操作是为了从数据库启动以后,从这个点开始进行数据的恢复

```sql

show master status \G;

```

主数据服务此时可以做一个备份,可以在服务器停止的情况下直接使用系统复制命令

```shell

tar -cvf data.tar data;

```

备份完成后,主数据恢复写操作

```sql

unlock tables;

```

> 主数据库的配置已经成功,如果my.cnf的MySQLd选项设置server-id参数,从服务器没有设置server-id,就会报错

```sql

start slave;

```

接下来编辑/etc/my.cnf文件,配置如下

```ini

[mysqld_multi]

mysqld = /usr/local/mysql/binmysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

user = root

[mysqld1]

port = 3306

log-bin = /usr/local/var/mysql1/mysql-bin

server-id = 1

[mysqld2]

port = 3307

socket = /temp/mysql2.sock

datadir = /usr/local/var/mysql2

log-bin = /usr/local/var/mysql2/mysql-bin

server-id = 2

[mysqld3]

port = 3308

socket = /temp/mysql3.sock

datadir = /usr/local/var/mysql3

log-bin = /usr/local/var/mysql3/mysql-bin

server-id = 3

[mysqld]

```

重启Master主服务器

```shell

mysqld_multi --defaults-extra-file=/etc/my.cnf stop 1-3

mysqld_multi --defaults-extra-file=/etc/my.cnf start 1-3

mysqld_multi --defaults-extra-file=/etc/my.cnf report 1-3

```

对数据库服务器做相应的设置,此时需要制定复制使用的用户,主数据的ip地址,端口以及开始复制的日志文件和位置等,具体设置如下.

```sql

mysql -uroot -p -P 3307 -S /tmp/mysql2.sock

show variables like '%log_bin%';

stop slave;

change master to

->master_host='127.0.0.1'

->master_user='repl'

->master_password='123'

->master_log_file='mysql-bin.000001'

->master_log_pos=120;

```

## 以下操作请结合上一节参考

在从服务器上执行show slave status \G命令查询服务器的状态

也可以执行show processlist \G查询从服务器的进程状态

此时,可以测试复制服务器的正确性