MySQL数据库应用实践

谁践踏了优雅 2022-02-20 01:01 208阅读 0赞

1.MySQL介绍

*  MySQL属于传统关系型数据库产品，它开放式的架构使得用户选择性很强，同时社区开发与维护人数众多。其功能稳定，性能卓越，且在遵守GPL协议的前提下，可以免费使用与修改，也为MySQL的推广与使用带来了更多的利好。在MySQL成长与发展过程中，支持的功能逐渐增多，性能也不断提高，对平台的支持也越来越多。
 *  MySQL是一种关系型数据库管理系统，关系型数据库的特点是将数据保存在不同的表中，再将这些表放入不同的数据库中，而不是将所有数据统一放在一个大仓库里，这样的设计增加了MySQL的读取速度，而且灵活性和可管理性也得到了很大提高。访问及管理MySQL数据库的最常用标准化语言为SQL结构化查询语言。

2.MySQL多实例介绍

*  在之前LNMP的讲解中，已经针对MySQL数据库进行了介绍，并说明了为什么要选择MySQL数据库，以及MySQL数据库在Linux系统下的多种安装方式，同时讲解了MySQL的二进制方式单实例安装，基础优化等内容，本节将为同学们讲解更为实用的MySQL多实例安装，主从复制集群等重要应用实践。

2.1 什么是MySQL多实例

*  简单的说，MySQL多实例就是在一台服务器上同时开启多个不同的服务器端口（如：3306，3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务器端口来提供服务。
 *  这些MySQL多实例共用一套MySQL安装程序，使用不同的my.cnf（也可以相同）配置文件，启动程序（也可以相同）和数据文件。在提供服务时，多实例MySQL在逻辑上看起来是各自独立的，它们根据配置文件的对应设定值，获得服务器相应数量的硬件资源。
 *  打个比方吧，MySQL多实例就相当于房子的多个卧室，每个实例可以看作一间卧室，整个服务器就是一套房子，服务器的硬件资源（CPU，Mem，Disk），软件资源（Centos操作系统）可以看作房子的卫生间，厨房，客厅，是房子的公用资源。
 *  其实很多网络服务都是可以配置多实例的，例如Nginx，Apache，Haproxy，Redis，Memcache等。这在门户网站使用得很广泛。

3.安装并配置多实例MySQL数据库

先使用多实例安装mysql  
1.二进制安装：

tar xf mysql-5.5.61-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
    ln -s /usr/local/mysql-5.5.61-linux-glibc2.12-x86_64 /usr/local/mysql
    
    chown -R mysql.mysql /usr/local/mysql
    mkdir -p /data/{3307,3308}/data
    chown -R mysql.mysql /data/

2.添加环境变量

echo 'export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/scripts' >> /etc/profile

3.初始化数据库

mysql_install_db --datadir=/data/3307/data --basedir=/usr/local/mysql --user=mysql
    mysql_install_db --datadir=/data/3308/data --basedir=/usr/local/mysql --user=mysql

如果是5.7版本的话用如下命令替换

mysqld --initialize-insecure -datadir=/data/3307/data --basedir=/usr/local/mysql --user=mysql
    mysqld --initialize-insecure -datadir=/data/3307/data --basedir=/usr/local/mysql --user=mysql

4.生成配置my.conf的配置文件，注意master和slave的server-id不能重复

vim /data/3306/my.cnf
    [client]
    port = 3306
    socket = /data/3306/mysql.sock
    
    [mysqld]
    user = mysql
    port = 3306
    socket = /data/3306/mysql.sock
    basedir = /usr/local/mysql
    datadir = /data/3306/data
    server-id = 2
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysqld_safe]
    log-error=/data/3306/mysql_3306.err
    pid-file=/data/3306/mysqld.pid

cp /data/3306/my.cnf /data/3307/my.cnf  
修改目录里所有的3306为3307

sed -i "s/3306/3307/g" /data/3307/my.cnf

将server-id修改为3

5.初始化MySQL多实例数据文件  
cd /usr/local/mysql/scripts/

./mysql_install_db --datadir=/data/3306/data --basedir=/usr/local/mysql 
    --user=mysql
    ./mysql_install_db --datadir=/data/3307/data --basedir=/usr/local/mysql 
    --user=mysql

6.启动MySQL多实例  
启动：\# `mysqld_safe --defaults-file=/data/3306/my.cnf &`  
如果第一次启动不了  
则需要跳过权限表

mysqld_safe --defaults-file=/data/3306/my.cnf  --skip-grant-tables &

mysqld\_safe --defaults-file=/data/3307/my.cnf --skip-grant-tables &

关闭：\# `mysqladmin. -uroot -p'' -S /data/3306/mysql.sock shutdown`

4.MySQL主从复制介绍

*  MySQL数据库的主从复制方案，与使用scp/rsync等命令进行的文件级别复制类似，都是数据的远程传输，只不过MySQL的主从复制是其自带的功能，无需借助第三方工具，而且，MySQL的主从复制并不是数据库磁盘上的文件直接拷贝，而是通过逻辑的binlog日志复制到要同步的服务器本地，然后由本地的线程读取日志里面的SQL语句，重新应用到MySQL数据库中。

MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)实践

一般来说都是通过 主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力 这样的方案来进行部署与实施的。  
如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmd4aWFveWFuMTI_size_16_color_FFFFFF_t_70]  
当web server1/2/3要写入数据时，则向mysql db Master(主服务器）发出写入请求（即写入到master），如果要进入读操作时，则只向从服务器 mysql DB Slave1或2或3发出读取请求。如此将原本读写在同一台服务器的工作量分摊到了一台负责写入，N台负责读取（大部分的网站都是读取请求远大于写入请求），从而从一定程度时实现了负载均衡（如果有N台从服务器，则由主从代理系统自动分配某个具体请求从哪一个从服务器读取）。  
读写分离是靠主从复制来实现的。即当一个数据写入到主服务器后，主服务器会将写入信息写入到binlog（二进制日志）里，同时同步（或异步或半同步）到从服务器里。从服务器根据主服务器传来的binlog，生成relay-log(中继日志），然后mysql服务器再用relay-log的信息将数据写入到数据库。  
如此做的优点除了实现负责均衡之外，还为我们保留了两份实时热备的数据binlog和relay-log。当服务器发生灾难时，我们可以用他们将数据恢复到任何一个时间点。  
准备：

主从复制;  
ntpdate [cn.pool.ntp.org][]  
hwclock --systohc  
这时在主库上操作  
vi /etc/my.cnf

bin_log=mysql  //开启bin_log
    server-id=1       //修改server-id
    log-bin-index=master-bin.index //开启binlog日志功能

systemctrl restart mysqld

创建一个用于让从数据库连接的用户
    mysql> create user 'copy'@'%' identified with mysql_native_password by 'Cloudbu@123';
    mysql> grant replication slave on *.* to 'copy'@'%';
    mysql -uroot -p123456 -e "show master status;"  
    // 查看主库的状态

mysql-bin.000003 | 199

在从库上

修改配置文件，必须指定中继日志的名称
    [mysqld]
    server_id=2
    relay-log=relay-log
    relay-log-index=relay-log.index
    
    
    [root@localhost ~]# systemctl stop mysql
    [root@localhost ~]# systemctl start mysql

就可以查看  
主库上的数据库同步到从库上

设定数据同步  
与主库的状态一致；

mysql>change master to
    MASTER_HOST='192.168.0.141',
    MASTER_PORT=3306,
    MASTER_USER='rcopy',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='mysql.000003',  //对比主库MASTER_LOG_FILE
    MASTER_LOG_POS=199;   //对比主库的MASTER_LOG_POS

然后开启同步开关

start slave;
    show slave status\G

注：\*\*如果Slave\_IO\_Running: 为NO  
可能是主从mysqluuid重复  
删除 auto.cnf 重启服务

Slave\_IO\_Running  
Slave\_SQL\_Running都为yes则配置成功\*\*  
测试  
在主库上新建表，在从库上就能同步到数据

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmd4aWFveWFuMTI_size_16_color_FFFFFF_t_70]: /images/20220220/9b1ad8db323d4a0b8afeb479c609b832.png
[cn.pool.ntp.org]: http://cn.pool.ntp.org