mysql主从复制

 

 

三、MySQL 主从备份案例

mysql>

[root@slave ~]# crontab -l

affiliates. Other names may be trademarks of their respective

22 server 127.127.1.0 // 手动增添此两行内容
23 fudge 127.127.1.0 startum 8

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

[root@slave ~]# vim /etc/my.cnf

                   Last_Errno: 0

[root@slave ~]# ntpdate 192.168.1.46
23 Aug 17:50:14 ntpdate[3407]: adjust time server 192.168.1.46 offset 0.000029 sec
[root@slave ~]# crontab -e

mysql>

从服务器复制了主服务器上的数据库,主从复制成功。

再登入从mysql,主服务器上新添的数额都出去了

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>

master---192.168.1.46

1 row in set (0.00 sec)

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

mysql> show databases;

享有服务器关闭防火墙、关闭selinux

| mysql              |

[root@master ~]# systemctl start ntpd

       Replicate_Ignore_Table:

[root@master ~]# chkconfig ntpd on

mysql> insert into fuck values(001,'abcd');

*/5 * * * * /usr/sbin/ntpdate 192.168.1.46

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

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

 #vi /etc/my.cnf

[root@master ~]# mysql -uroot -p

               Last_SQL_Error:

给从服务器授权

Enter password:

MariaDB [(none)]> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.46
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 613
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
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: 613
Relay_Log_Space: 821
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
1 row in set (0.00 sec)

 

若在 从服务器 start slave; 之后察觉 Slave_IO_Running: No 、Slave_SQL_Running: Yes ,则
需要先 stop slave; 重新 change master to …; 再 start slave;

              Master_SSL_Cert:

MariaDB [(none)]> change master to
-> master_host='192.168.1.46', 
-> master_port=3306,
-> master_user='myslave',
-> master_password='123123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=613;
Query OK, 0 rows affected (0.01 sec)

注:主从机器mysql版本同样,并都早已先河化表并设置了root口令。

2020欧洲杯官方投注-2020欧洲杯官方投注网址 1

| mysql-bin.000002 |      251 |              |                  |

二、主从复制原理原理
MySQL 主从复制的类型:
·基于语句的复制
·基于行的复制
·混合类型的复制

Last login: Sun Jul 24 23:36:55 2016 from 10.10.10.1

10 relay-log=relay-log-bin
11 relay-log-index=slave-relay-bin.index
12 server-id=12 #基本服务器的 server-id  不可能长期以来!

4,配置从服务器

后生可畏台主服务器能够做七个从服务器,从服务器也足以当做主服务器,再做五个从服务器,依此类推....

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

[root@slave ~]# systemctl restart mariadb
[root@slave ~]# mysqladmin -uroot password 123123
[root@slave ~]# mysql -uroot -p123123

4 rows in set (0.02 sec)

陈设 MySQL Master  主服务器

 

两台CentOS7服务器

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

                  Master_User: mysync

从服务器上进行时间同步

| test               |

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

境况是原先布署lnmp+keepalived负载均衡的2台机器,IP分别是10.10.10.13和10.10.10.14.

透过查阅 slave  状态,确定保证 Slave_IO_Running: Yes Slave_SQL_Running: Yes

1 row in set (0.00 sec)

[root@master ~]# vim /etc/my.cnf

Your MySQL connection id is 2

成登时间同步处境 , 在主服务器上安装配置 NTP  时间一齐服务器

| test               |

10 log-bin=mysql-bin
11 log-slave-updates=true #手动增多,开启从日记
12 server-id=11

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

在 MySQL  主服务器创造 test db数据库

| information_schema |

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
5 rows in set (0.00 sec)

                Last_IO_Errno: 0

2020欧洲杯官方投注-2020欧洲杯官方投注网址 2

              Until_Condition: None

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
5 rows in set (0.00 sec)

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

注意:

tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      1961/sshd          

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

tcp        0      0 0.0.0.0:80                  0.0.0.0:*                   LISTEN      11021/nginx        

在从服务器查看是或不是数据同步

          Exec_Master_Log_Pos: 251

豆蔻梢头 、集团网址单个 MySQL 难点解析
在小卖部网址中,后端 MySQL 数据库唯有风流洒脱台时,会有以下难题:单点故障,服务不足
用;无法管理多量的产出数据央求;数据错过将招致大磨难。
改建方式:
追加 MySQL 数据库服务器,对数据开展备份,变成主备。
担确认保障备 MySQL 数据库服务器是如出风度翩翩辙的
主服务器宕机了,备份服务器继续做事,数占有保险
MySQL 主从复制与读写抽离是周到相关

Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name  

slave---192.168.1.47

1 row in set (0.00 sec)

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

                Connect_Retry: 60

1、MySQL 从服务器开启 I/O 线程,向主服务器供给数据同步(获取二进制日志卡塔 尔(英语:State of Qatar)
2、MySQL 主服务器开启 I/O 线程回应从服务器
3、从服务器得到主的二进制日志写入中继日志
4、从服务器开启 SQL 线程将日志内容施行,完结数量同步

2020欧洲杯官方投注-2020欧洲杯官方投注网址,               Master_SSL_Key:

MariaDB [(none)]> grant replication slave on *.* to 'myslave'@'192.168.1.%' identified by '123123';
Query OK, 0 rows affected (0.00 sec)

 mysql>GRANT REPLICATION SLAVE ON *.* to 'sync'@'%' identified by '123456';

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 613 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

|    1 | abcd |

布置从服务器

 

[root@slave ~]# yum -y install mariadb-*

tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      1821/cupsd         

[root@master ~]# systemctl restart mariadb

关键是Slave_IO及Slave_SQL进度必须求都YES

在[mysqld]模块入手动增加

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

mysql> use fuck;

Server version: 5.1.73-log Source distribution

 

Database changed

Your MySQL connection id is 2

 #mysql -uroot -p  

mysql> select * from fuck;

mysql> change master to master_host='10.10.10.13',master_user='sync',master_password='123456',master_log_file='my

Welcome to the MySQL monitor.  Commands end with ; or g.

 

# service mysqld restart

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

Oracle is a registered trademark of Oracle Corporation and/or its

              Replicate_Do_DB:

| information_schema |

tcp        0      0 ::1:631                     :::*                        LISTEN      1821/cupsd         

           Master_SSL_CA_Path:

 

              Master_Log_File: mysql-bin.000002

 

sql-bin.000002',master_log_pos=251;

Welcome to the MySQL monitor.  Commands end with ; or g.

Query OK, 0 rows affected (0.00 sec)

mysql> create database fuck;

 

Enter password:

tcp        0      0 ::1:25                      :::*                        LISTEN      2115/master

mysql> show master status;

        Seconds_Behind_Master: 0

Stopping mysqld:  [  OK  ]

 

owners.

| Database           |

mysql> show databases;

 

本文由2020欧洲杯官方投注-2020欧洲杯官方投注网址发布于win7,转载请注明出处:mysql主从复制

相关阅读