MySQL主从复制配置教程-优快云博客

一、Master机(192.168.146.138)：

1.安装mysql

[root@mysql_master~]# yum install –y mysql-server msyql mysql-devel

2.vi /etc/my.cnf

[msyqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbilic-links=0
auto_increment_offset=1
auto_increment_increment=2
log-bin=mysql-bin
server-id=138
[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysql/mysqld.pid
replicate-do-db=all

3.创建目录

mkdir –p /data/mysql
chown –R mysql.mysql/data/mysql

4.启动mysql:/etc/init.d/msyqld start

若报错查看/var/log/mysql.log找错误原因

备注：（我出现mysql[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host'doesn't exist 解决办法：mysql_install_db –user=mysql & datadir=/data/mysql）

二：Slave机(192.168.146.137)

同样先安装：[root@mysql_bak ~]# yum install–y mysql-server msyql mysql-devel

2.vi /etc/my.cnf

[mysql]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=msyql
symbolic-links=0
log-bin=msyql-bin
server-id=137
auto_increment_offset=2
auto_increment_increment=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld.pid
master-host=192.168.146.138
master-user=backup
master-password=123456
master-port=3306
master-connect-retry=60
replicate-do-db=all

3.启动mysql：/etc/init.d/mysqldstart

三、Master机创建备份用户

mysql>grantreplication slave on *.*  to ‘backup’@’%’ identified by‘123456’;
mysql>showmaster status;
+------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      250 |             |                  |
+------------------+----------+--------------+------------------+

四、Slave机设置备份参数

mysql> change master to ->master_host='192.168.146.138',master_user='backup',master_password='123456',master_log_file='msyql-bin-000004',master_log_pos=250;
mysql>slave start;
mysql> show slave status\G;
*************************** 1. row***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host:192.168.146.138
                 Master_User: backup
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000005
         Read_Master_Log_Pos: 106
              Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251                #>=上面的250
       Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
  .....

mysql主从备份