【数据库私房菜 | 03】项目私房菜——MySQL的项目实战（一）

🗺️博客地图

📍一、MySQL的主从环境

🖈（一）MySQL主从服务原理

🖈（二）MySQL主从服务实战​​​​​​​

📍二、主主从服务器

🖈（一）背景

🖈（二）主服务器1操作配置

🖈（三）主服务器2操作配置

🖈（四）从服务器操作配置

🖈（五）检验测试

---

一、MySQL的主从环境

MySQL从服务器可以做为主服务器的备份服务器，主从环境可以构建集群化。

（一）MySQL主从服务原理

主数据库服务器执行使数据库结构发生改变的sql语句时，触发I/O线程，向从数据库服务器传输二进制日志，从数据库服务器接收到二进制日志后，将其改名为relay_bin.index和relay_bin.000001中继日志，当获取完所有的二进制日志文件后，I/O线程会自动休眠，以减少资源消耗，同时，会唤醒sql线程，sql线程会将中继日志转为数据库内容。

（二）MySQL主从服务实战

注意：建立主从服务器之前，两边库需要一致，若不一致，可以mysqldump备份到从服务器还原

• 主服务器操作配置

yum -y install mysql mysql-server
vim /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin
    server-id=15

    #一个主从环境下主从服务器要有自己的服务编号，编号一般以IP地址主机位作为编号，同一个主从环境中，server-id不能冲突，否则会报错

service mysqld start
mysqladmin -uroot password 123
mysql -uroot -p123
mysql> grant all on *.* to 'master'@'%' identified by '123'; 
        #创建从服务器登录用户

#赋予远程登录用户复制（replication）与同步（slave）的权限

grant replication slave on *.* to '用户'@'%' identified by '密码';

#查看二进制日志文件名和大小

mysql> show master status; 

• 从服务器操作配置

yum -y install mysql mysql-server
vim /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin
    server-id=16

service mysqld start
mysqladmin -uroot password 123
mysql -uroot -p123
mysql> change master to master_user='用户',master_password='密码',master_host='主服务器IP',master_log_file='二进制文件',master_log_pos=文件空间大小; 

注意：若上述命令输入错误，可先停掉服务，然后去/var/lib/mysql/目录下删除master.info、mysqld-relay-bin.000001、mysqld-relay-bin.index、relay-log.info，开启服务后重新执行上述正确命令即可

mysql> change master to master_user='master',master_password='123',change master to master_host='192.168.43.20',master_log_file='mysql-bin.000003',master_log_pos=383;

mysql> show slave status\G;

mysql> start slave;            #开启主从同步
mysql> stop slave;            #关闭主从同步结果：

检验测试：

①主服务器：

创建数据库，表，插入数据；

mysql> create database li;

mysql> create table users(id int; name char(200));

mysql> insert into users values (1,'zhang');

②从服务器：

mysql> selecte * from 库名.表名；

mysql> selecte * from li.users；

二、主主从服务器

（一）背景

生产环境中，会出现存在多个主数据库来承担不同项目的访问，从服务器所消耗的资源远比主服务器承担访问的压力小，如果每台主服务器配置一台从服务器会造成资源浪费，所以我们常常采用主主从的方式来搭建数据库的备份环境。

（二）主服务器1操作配置

yum -y install mysql mysql-server
vim /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin        #mysql_bin可以随意指定，但在从服务器中也要随之改变

    server-id=20                #同一个主从环境中，server-id不能冲突，建议使用当前IP主机名（2-254）

    [mysqld_safe]

    log-error=/var/log/mysqld.log

    pid-file=/var/run/mysqld/mysqld.pid

# 启动mysqld服务
service mysqld start

# 设置root密码
mysqladmin -uroot password 123

# 进入数据库
mysql -uroot -p123

# 创建同步用户并赋予权限
mysql> grant all on *.* to 'master'@'%' identified by '123';
mysql> show master status;
        #查看二进制日志文件名与pos值

（三）主服务器2操作配置

yum -y install mysql mysql-server
vim /etc/my.cnf
    [mysqld]
    log-bin=mysql-bin        #mysql_bin可以随意指定，但在从服务器中也要随之改变

    server-id=30                #同一个主从环境中，server-id不能冲突，建议使用当前IP主机名（2-254）

    [mysqld_safe]

    log-error=/var/log/mysqld.log

    pid-file=/var/run/mysqld/mysqld.pid

# 启动mysqld服务
service mysqld start

# 设置root密码
mysqladmin -uroot password 123

# 进入数据库
mysql -uroot -p123

# 创建同步用户并赋予权限
mysql> grant all on *.* to 'master'@'%' identified by '123';
mysql> show master status;
        #查看二进制日志文件名与pos值

（四）从服务器操作配置

yum -y install mysql mysql-server

# 在主配置文件中追加如下mysqld_multi 区域（注意：安装后先不启动，配置完成后再启动）

vim /etc/my.cnf
    #添加区域
    [mysqld_multi]
    mysqld=/usr/bin/mysqld_safe
    mysqladmin=/usr/bin/mysqladmin
    log=/tmp/multi.log

    [mysqld20] 
    port=3306
    pid-file=/var/lib/mysqla/mysqld.pid
    datadir=/var/lib/mysqla
    socket=/var/lib/mysqla/mysql.sock
    server-id=40
    user=mysql

    [mysqld30] 
    port=3307
    pid-file=/var/lib/mysqlb/mysqld.pid
    datadir=/var/lib/mysqlb
    socket=/var/lib/mysqlb/mysql.sock
    server-id=40
    user=mysql

        ……        #后面还可以添加多个[mysqld数字]区域

#创建目录（创建目录必须和主配置文件中的一致）
mkdir /var/lib/mysqla
mkdir /var/lib/mysqlb

#更改所有者所属组
chown -R mysql.mysql /var/lib/mysqla/ 
chown -R mysql.mysql /var/lib/mysqlb/

#初始化
mysql_install_db --datadir=/var/lib/mysqla --user=mysql
mysql_install_db --datadir=/var/lib/mysqlb --user=mysql

#启动mysqld_multi
mysqld_multi --defaults-file=/etc/my.cnf start 主服务器1的id号
mysqld_multi --defaults-file=/etc/my.cnf start 主服务器2的id号

#赋予数据库密码的方式
mysqladmin -uroot password 123 -S /var/lib/mysqla/mysql.sock
mysqladmin -uroot password 123 -S /var/lib/mysqlb/mysql.sock

#使用用用户名密码进入数据库（必须指定套接字文件才能进入）
mysql -uroot -p123 -S /var/lib/mysqla/mysql.sock
mysql> change master to master_user='master',master_password='123',master_host='192.168.43.20',master_log_file='mysql_bin.000001',master_log_pos=371;

mysql> start slave;                #开启同步
mysql> show slave status\G;

#确认i/o与SQL线程是否开启，若i/o线程为NO，则说明change master to命令出错，SQL线程为NO。则说明主从服务器的数据库未同步。

mysql -uroot -p123 -S /var/lib/mysqlb/mysql.sock
mysql> change master to master_user='master',master_password='123',master_host='192.168.43.30',master_log_file='mysql_bin.000001',master_log_pos=106;

mysql> start slave;                #开启同步
mysql> show slave status\G;

（五）检验测试

①主服务器2

在主数据库2下执行使数据库结构发生改变的sql语句

mysql -uroot -p123
mysql> create database li;
mysql> use li;
mysql> create table users (id int,name char(200));
mysql> insert into users values (1,"wang");

②从服务器

mysql  -uroot  -p密码  -P  3306  -S  /var/lib/mysqlb/mysql.sock

        mysql> show  databases;

        mysql> use  users;

        mysql> show  tables;

        mysql> select * from li.users;