# 单机主从复制 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查询从服务器的进程状态 此时,可以测试复制服务器的正确性