快捷搜索:

您的位置:澳门新葡4473网站 > 热门贴子 > 新葡亰平台娱乐mysql主从复制

新葡亰平台娱乐mysql主从复制

发布时间:2019-11-03 03:28编辑:热门贴子浏览(180)

    一 、企业网站单个 MySQL 问题分析
    在企业网站中,后端 MySQL 数据库只有一台时,会有以下问题:单点故障,服务不可
    用;无法处理大量的并发数据请求;数据丢失将造成大灾难。
    改造办法:
    增加 MySQL 数据库服务器,对数据进行备份,形成主备。
    确保准备 MySQL 数据库服务器是一样的
    主服务器宕机了,备份服务器继续工作,数据有保障
    MySQL 主从复制与读写分离是密切相关

    环境是先前配置lnmp+keepalived负载均衡的2台机器,IP分别是10.10.10.13和10.10.10.14.

    新葡亰平台娱乐 1

    1,登录系统

    一台主服务器可以做多个从服务器,从服务器也可以充当主服务器,再做多个从服务器,以此类推....

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

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

    [root@hd3 ~]# netstat -nltp

    新葡亰平台娱乐 2

    Active Internet connections (only servers)

    1、MySQL 从服务器开启 I/O 线程,向主服务器请求数据同步(获取二进制日志)
    2、MySQL 主服务器开启 I/O 线程回应从服务器
    3、从服务器得到主的二进制日志写入中继日志
    4、从服务器开启 SQL 线程将日志内容执行,实现数据同步

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

    三、MySQL 主从备份案例

    tcp        0      0 127.0.0.1:9000              0.0.0.0:*                   LISTEN      9471/php-fpm       

    两台CentOS7服务器

    tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      9362/mysqld        

    master---192.168.1.46

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

    slave---192.168.1.47

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

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

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

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

    tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      2115/master        

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

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

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

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

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

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

    [root@master ~]# systemctl start ntpd

     

    [root@master ~]# chkconfig ntpd on

    注:主从机器mysql版本一致,并都已经初始化表并设置了root口令。

    从服务器上进行时间同步

     

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

    2、修改主从服务器配置文件的2个字段

    [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

     #vi /etc/my.cnf

    [root@slave ~]# crontab -l

           [mysqld]

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

           log-bin=mysql-bin   //启用二进制日志

    配置 MySQL Master  主服务器

           server-id=1      //服务器唯一ID,默认是1,这里主的用的默认,从服务器改为2。之后 

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

    # service mysqld restart

    在[mysqld]模块下手动添加

    Stopping mysqld:  [  OK  ]

    10 log-bin=mysql-bin
    11 log-slave-updates=true #手动添加,开启从日志
    12 server-id=11

    Starting mysqld:  [  OK  ]

    [root@master ~]# systemctl restart mariadb

     

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

    3,在主服务器上建立帐户并授权slave:

    给从服务器授权

     #mysql -uroot -p  

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

    Enter password:

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

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

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

    Your MySQL connection id is 2

    配置从服务器

    Server version: 5.1.73-log Source distribution

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

     

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

    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

    10 relay-log=relay-log-bin
    11 relay-log-index=slave-relay-bin.index
    12 server-id=12 #主从服务器的 server-id  不能相同!

     

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

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

    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)

    affiliates. Other names may be trademarks of their respective

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

    owners.

    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)

     

    通过查看 slave  状态,确保 Slave_IO_Running: Yes Slave_SQL_Running: Yes

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

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

     

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

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

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

    查询master的状态,记录下 FILE Position 的值

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

    mysql> show master status;

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

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

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

    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

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

     

    | mysql-bin.000002 |      251 |              |                  |

    注意:

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

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

    1 row in set (0.00 sec)

     

    4,配置从服务器

    # mysql -uroot -p         

    Enter password:

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

    Your MySQL connection id is 2

    Server version: 5.1.73-log Source distribution

     

    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

     

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

    affiliates. Other names may be trademarks of their respective

    owners.

     

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

     

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

    sql-bin.000002',master_log_pos=251;

    Query OK, 0 rows affected (0.03 sec)

     

    mysql> start slave;

    Query OK, 0 rows affected (0.00 sec)

     

    mysql> show slave statusG

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

                   Slave_IO_State: Waiting for master to send event

                      Master_Host: 10.10.10.13

                      Master_User: mysync

                      Master_Port: 3306

                    Connect_Retry: 60

                  Master_Log_File: mysql-bin.000002

              Read_Master_Log_Pos: 251

                   Relay_Log_File: mysqld-relay-bin.000002

                    Relay_Log_Pos: 251

            Relay_Master_Log_File: mysql-bin.000002

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

                  Relay_Log_Space: 407

                  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:

    1 row in set (0.00 sec)

    关键是Slave_IO及Slave_SQL进程一定要都YES

     

     

    5,主从复制测试

    先在主mysql上:

    mysql> create database fuck;

    Query OK, 1 row affected (0.03 sec)

     

    mysql> use fuck;

    Database changed

    mysql>

    mysql>

    mysql> create table fuck(id int(3),name char(10));

    Query OK, 0 rows affected (0.08 sec)

     

    mysql>

    mysql>

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

    Query OK, 1 row affected (0.00 sec)

     

    mysql> show databases;

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

    | Database           |

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

    | information_schema |

    | fuck               |

    | mysql              |

    | test               |

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

    4 rows in set (0.03 sec)

     

    mysql>

     

    再登录从mysql,主服务器上新增的数据都出来了

    mysql> show databases;

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

    | Database           |

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

    | information_schema |

    | fuck               |

    | mysql              |

    | test               |

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

    4 rows in set (0.02 sec)

     

    mysql> use fuck;

    Database changed

    mysql> select * from fuck;

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

    | id   | name |

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

    |    1 | abcd |

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

    1 row in set (0.00 sec)

     

    mysql>

     

    本文由澳门新葡4473网站发布于热门贴子,转载请注明出处:新葡亰平台娱乐mysql主从复制

    关键词:

上一篇:__dbg.h

下一篇:没有了