mysql主从复制Centos7.1

 

mysql主从备份

1.0 实验环境：

   主服务器：mysql-mysql  192.168.1.138

             mysql-client   192.168.1.139  【mysql由yum安装】

            

1.1所有服务器关闭firewalld或者进行规制设置

建立时间同步环境，在主服务器上安装配置NTP时间同步服务器

[root@mysql ~]# yum -y install ntp

[root@mysql ~]# cp -p /etc/ntp.conf /etc/ntp.conf.origin

[root@mysql ~]# vim /etc/ntp.conf

13 restrict 127.0.0.1

 14 restrict ::1

 15 server 127.127.1.0

 16 fudge 127.127.1.0 startum 8

【代表可以有8个子服务器相连】

【手动添加】

[root@mysql ~]# systemctl  start ntpd

[root@mysql ~]# ntpq -p

     remote           refid      st t when poll reach   delay   offset  jitter

==============================================================================

*LOCAL(0)        .LOCL.           5 l   57   64  377    0.000    0.000   0.000

[root@mysql ~]# ss -tlunp|grep ntp【查看ntpd连接状态】

tcp    UNCONN     0      0          192.168.1.138:123                   *:*      users:(("ntpd",14750,19))

tcp    UNCONN     0      0              127.0.0.1:123                   *:*      users:(("ntpd",14750,18))

tcp    UNCONN     0      0                      *:123                   *:*      users:(("ntpd",14750,16))

tcp    UNCONN     0      0                    ::1:123                  :::*      users:(("ntpd",14750,21))

tcp    UNCONN     0      0        fe80::20c:29ff:fee6:af2:123                  :::*      users:(("ntpd",14750,20))

tcp    UNCONN     0      0                     :::123                  :::*      users:(("ntpd",14750,17))

[root@mysql ~]# chkconfig ntpd on

注意：正在将请求转发到“systemctl enable ntpd.service”。

ln -s '/usr/lib/systemd/system/ntpd.service' '/etc/systemd/system/multi-user.target.wants/ntpd.service'

 

 

1.2【在从服务器上同步时间】

[root@client ~]# yum -y install ntpdate

[root@client ~]# ntpdate 192.168.1.138【主ip】

14 May 05:56:09 ntpdate[10138]: step time server 192.168.1.138 offset -89795.688284 sec

【注意主从mysql尽量保证版本相同】

 

1.3  配置mysql mysql主服务器

[root@mysql ~]# vim /etc/ntp.conf

20 log-bin=mysql-bin

 21 log-slave-updates=true【手动添加，开启从日志】

 22 server-id       =11

[root@mysql ~]# /etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS!

 

1.4 给从服务器授权

[root@mysql ~]# mysql -uroot -p123123

mysql> grant replication slave on *.* to 'myslave'@'192.168.1.%' identified by '123123';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000037 |      337 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

1.5 配置从服务器

[root@client ~]# vi /etc/my.cnf

57 server-id       = 12【主从服务器的server-id不能相同】

 58 relay-log=relay-log-bin

 59 relay-log-index=slave-relay-bin.index

 [root@client ~]# /etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL. SUCCESS!

[root@client ~]# mysql -uroot -p123123

MySQL [(none)]> change master to

    -> master_host='192.168.1.138',master_user='myslave',master_password='123123',master_log_file='mysql-bin.000013',master_log_pos=107;

Query OK, 0 rows affected (0.02 sec)

 

MySQL [(none)]> start slave;

Query OK, 0 rows affected (0.02 sec)

MySQL [(none)]> show slave status\G

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

               Slave_IO_State:

                  Master_Host: 192.168.1.138

                  Master_User: myslave

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 337

               Relay_Log_File: relay-log-bin.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File: mysql-bin.000002

             Slave_IO_Running: Yes【必须是yes】

            Slave_SQL_Running: Yes【必须是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: 337

              Relay_Log_Space: 107

              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: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 1593

                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1

1 row in set (0.00 sec)

 

1.6  测试：

在mysql主服务器创建db_test数据库

mysql> create database db_test;

Query OK, 1 row affected (0.00 sec)

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| auth               |

| client             |

| db_test            |

| haoli              |

| imployee_salary    |

| libai              |

| mysql              |

| performance_schema |

| test               |

| yunjisuan          |

+--------------------+

11 rows in set (0.00 sec)

mysql> use db_test;

Database changed

mysql> create table users(user_name char(20),user_passwd int(10));

Query OK, 0 rows affected (0.01 sec)

 

mysql> desc users;

+-------------+----------+------+-----+---------+-------+

| Field       | Type     | Null | Key | Default | Extra |

+-------------+----------+------+-----+---------+-------+

| user_name   | char(20) | YES  |     | NULL    |       |

| user_passwd | int(10)  | YES  |     | NULL    |       |

+-------------+----------+------+-----+---------+-------+

2 rows in set (0.00 sec)

【分别在俩台主从服务器查看是否数据同步】

【从服务器】

MySQL [(none)]> show databases;【查看从已经有db_test】

+--------------------+

| Database           |

+--------------------+

| information_schema |

| auth               |

| client             |

| db_test            |

| haoli              |

| imployee_salary    |

| libai              |

| mysql              |

| performance_schema |

| test               |

| yunjisuan          |

+--------------------+

11 rows in set (0.00 sec)

MySQL [(none)]> desc db_test.users;

+-------------+----------+------+-----+---------+-------+

| Field       | Type     | Null | Key | Default | Extra |

+-------------+----------+------+-----+---------+-------+

| user_name   | char(20) | YES  |     | NULL    |       |

| user_passwd | int(10)  | YES  |     | NULL    |       |

+-------------+----------+------+-----+---------+-------+

2 rows in set (0.00 sec)

【若在从服务器start slave;之后发现slave_io_running:NO、slave_sal_running:yes,则需要先stop slave；重新change master to master_log_file='mysql-bin.000013',master_log_pos=107;再 start  slave】

 

补充：

1、mysql.pid文件是在重启mysql的时候会创建的，如果创建失败就会提示没有pid文件这个错误，发现导致此错误有很多种原因，像权限、端口被占用、参数文件配置错误等等

2、mysql启动失败此进程杀死后自启动，导致端口号一直被占用，

[root@client data]# ps -ef|grep mysql【查看端口】

root       4866   2609  0 16:33 pts/1    00:00:00 grep --color=auto mysql

解决办法在，data下创建pid文件，随意添加一个没有被占用的pid号即可

[root@client data]# vi client.pid

[root@client data]# /etc/init.d/mysqld start

Starting MySQL SUCCESS!

3、如果还是不行，你就需要看看配置文件/etc/my.cnf里面的配置了，将port、datadir、basedir、 socket前的#去掉

4、去mysql的数据目录/data看看，如果存在mysql-bin.index，就赶快把它删除掉吧，它就是罪魁祸首了。

5、在杀死进程mysql时[root@client ~]# ps -ef|grep mysql

root      18970  18946  0 16:50 pts/0    00:00:00 grep --color=auto mysql

，出现xshell掉线，没有找到原因？

【还可以删除date下的ibdata1 ib_logfile0  ib_logfile1  】

6、[root@client ~]# tail -f /usr/local/mysql/data/localhost.localdomain.err【查看动态错误日志】

7、报错。  Slave_IO_Running: no是因为进入数据库的用户不是指定允许用户myslave

MySQL [(none)]> show master status;【查看master状态】

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000013 |      107 |              |                  |

+------------------+----------+--------------+------------------+

MySQL [(none)]> slave stop;【关闭slave】

Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> change master to master_log_file='mysql-bin.000013',master_log_pos=107;

Query OK, 0 rows affected (0.02 sec)

MySQL [(none)]> slave start;【开启slave】

Query OK, 0 rows affected (0.00 sec)

修改后重启mysql 。好了