MySQL入门第十二天（数据库配置与部署）

敲代码的江北

于 2022-02-20 16:36:05 发布

阅读量522

点赞数

分类专栏： MySQL数据库文章标签： mysql 数据库 database centos linux

本文链接：https://blog.csdn.net/weixin_55597816/article/details/123031602

版权

MySQL数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

12.1MySQL配置文件

（1）配置区段

MySQL配置文件my.cnf的区段：

[mysqld]：MySQL服务的配置

[client]：客户端的配置

（2）基本配置

MySQL服务端基本配置

SQL模式

默认字符集

默认校对集

默认存储引擎

sql_mode：服务器SQL模式，用于定义MySQL支持的SQL语法以及执行哪种数据验证检查。除非有特殊需求，一般不需要配置，使用默认值即可。

character_set_server：服务器的默认字符集，默认为latin1。

collation_server：服务器的默认校对集，默认为latin1_swedish_ci。

explicit_defaults_for_timestamp：服务器对TIMESTAMP列的默认值和NULL值的处理方式，默认为0。设为1时，TIMESTAMP列可以保存NULL值。

max_connections：允许的客户端最大同时连接数，默认为151。open_files_limit：操作系统允许mysqld打开的文件数，默认为5000。

default_storage_engine：默认存储引擎，默认为InnoDB。

（3）内存和优化配置

key_buffer_size：索引缓冲区大小，由所有线程共享。增加大小可以获得更好处理的索引，默认值为8M。

table_open_cache：所有线程的打开表的缓存数量，用于更快地访问表内容，默认为2000。

sort_buffer_size：为每个需要进行排序的会话分配的缓冲区大小，默认为256K。

read_buffer_size：对MyISAM表执行顺序扫描的每个线程分配的缓冲区，默认为128K。

thread_cache_size：服务器应缓存多少线程以供重用，默认为-1（自动调整大小）。

query_cache_size：表示查询缓冲区的大小，默认为1M。

read_buffer_size：对MyISAM表执行顺序扫描的每个线程分配的缓冲区，默认为128K。

thread_cache_size：服务器应缓存多少线程以供重用，默认为-1（自动调整大小）。

query_cache_size：表示查询缓冲区的大小，默认为1M。

（4）日志配置

MySQL日志的分类：

错误日志

常规日志

二进制日志

慢查询日志

log-error：错误日志的文件路径，保存MySQL启动、运行或停止时的日志信息。

general-log：是否开启常规日志，用于记录客户端连接和执行的SQL语句。

general-log_file：常规日志的文件路径。

log_slow_queries：慢查询日志文件路径，用于记录超过long_query_time时间或没有使用索引的查询。

long_query_time：当查询时间超过指定的值，就会记录到慢查询日志中。

log_queries_not_using_indexes：是否在慢查询日志中记录未使用索引的查询。

log-bin：二进制日志的保存路径，主要用于复制环境和数据恢复。

max_binlog_size：二进制日志单个文件的大小限制。

expire_logs_days：自动清除超过指定天数的过期日志。

12.2数据备份与还原

（1）数据备份

mysqldump工具支持备份单个或多个数据库。

备份单个数据库语法格式：

mysqldump -uusername -ppassword dbname [tbname1 [tbname2…]]

备份多个数据库语法格式：

mysqldump -uusername -ppassword --databases dbname1 [dbname2 …]

备份所有数据库语法格式：

mysqldump -uusername -ppassword --all-databases

保存输出结果：

mysqldump命令会将结果直接输出到屏幕中，为了保存输出结果，需要在命令后面加上“>filename.sql”，表示将输出重定向到filename.sql文件（文件名前面可以加上路径）。

（2）数据还原

1.mysqldump命令可以使用输入重定向读取SQL脚本还原数据，在mysql命令的末尾加上“<filename.sql”。

mysql -uusername -ppassword [dbname] <filename.sql

[dbname]：若SQL脚本中包含选择数据库的命令，则无需指定。

导入单个数据库的备份：需要指定[dbname]。

导入多个数据库的备份：无需指定[dbname]。

2.使用MySQL客户端命令source。

source 文件路径

（3）二进制日志

二进制日志：记录MySQL数据库的变化。

如：表的创建、对表数据的更改。

用于记录涉及修改的SQL、数据修改的行变化、执行时间等信息

二进制日志作用：搭建复制架构（如主从复制）、数据恢复等场景。

开启二进制日志：

在my.cnf配置文件的“[mysqld]”区段中增加配置。

[mysqld]
……
log-bin=binlog
server-id=1

log-bin=binlog：开启二进制日志，文件名为binlog。

server-id：服务器id（用于在复制架构中区分服务器）

重启MySQL服务使配置生效

[root@localhost ~]# systemctl restart mysqld

查看二进制日志的开启情况

mysql> SHOW VARIABLES LIKE 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)

查看二进制日志文件

[root@localhost ~]# ls /var/lib/mysql | grep binlog
binlog.000001
binlog.index

查看索引文件

[root@localhost ~]# cat /var/lib/mysql/binlog.index
./binlog.000001

binlog.000001文件保存二进制日志的内容。

二进制日志文件的命名规则：

MySQL 服务重新启动

文件大小达到了 max_binlog_size 配置的上限（默认为 1GB ）

满足其中一个条件，就会创建一个新日志文件，

新日志文件的扩展名加1递增，即binlog.000002。

查看二进制日志文件

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       154 |
+---------------+-----------+
1 row in set (0.00 sec)

Log_name：日志文件名

File_size：文件大小

查看二进制日志，需要转换为文本格式的SQL脚本

[root@localhost ~]# cd /usr/local/mysql/bin
[root@localhost bin]# ./mysqlbinlog /var/lib/mysql/binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ROLLBACK/*!*/;
BINLOG '
u1VSWw8BAAAAdwAAAHsAAAABAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAACgoKKioAEjQAAaQ8NVo=
'/*!*/;
# at 123
#180723  5:35:55 server id 1  end_log_pos 154 CRC32 0x7e392ba9 
 ︙（此处省略一些显示结果）

将转换结果导入到MySQL

[root@localhost bin]# ./mysqlbinlog /var/lib/mysql/binlog.000001 | 
mysql -uroot -p123456

管道符号“|”：用于将左边命令的输出结果，作为右边的命令的输入。

二进制日志备份的优势：

当数据库中的数据量非常大时，如果采用完整备份的方式，每次备份的数据量会非常大，速度也非常慢。

二进制日志记录了数据库发生过的变化，定期备份二进制日志文件就可以实现增量备份的效果。

mysqlbinlog命令还有一些选项。

mysqlbinlog [options] log-files

• log-files ：日志文件路径

• options ：可选的选项。

常用的options选项：

--start-date和--stop-date：指定开始日期和结束时期。

--start-position和--stop-position：指定开始位置和结束位置。

若要恢复到指定位置：

可以在输出的日志结果中通过“at 位置”来查看，

也可以使用SHOW MASTER STATUS查看当前位置。

暂停二进制日志

mysql> SET sql_log_bin = 0;

只针对当前会话有效，当退出MySQL客户端后，

下次登录就会恢复记录。

立即恢复二进制日志的记录

mysql> SET sql_log_bin = 1;

删除二进制日志

# 方式1 删除所有二进制日志文件
mysql> RESET MASTER;
# 方式2 删除创建时间早于binlog.000002的所有日志文件
mysql> PURGE MASTER LOGS TO 'binlog.000002';
# 方式3 删除2018年7月23日前的二进制日志
mysql> PURGE MASTER LOGS BEFORE '20180723';

方式二不删除binlog.000002文件本身，方式三不删除binlog.000002文件本身

查看删除结果：SHOW BINARY LOGS

12.3多实例部署

什么是多实例：

在一台服务器中运行多个MySQL服务，通过监听不同的端口号来区分。

多实例部署的意义：

当一台服务器的资源有剩余时，通过多实例部署，可以利用这些资源来提供更多服务。

更改配置文件

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

增加如下配置

[mysqld@replica01]
port=3307
datadir=/var/lib/mysql-replica01
socket=/var/lib/mysql-replica01/mysql.sock
log-error=/var/lib/mysql-replica01/mysqld.log
[mysqld@replica02]
port=3308
datadir=/var/lib/mysql-replica02
socket=/var/lib/mysql-replica02/mysql.sock
log-error=/var/lib/mysql-replica02/mysqld.log

为新增的两个实例初始化数据库

[root@localhost ~]# cd /usr/local/mysql/bin
[root@localhost bin]# ./mysqld --initialize-insecure --datadir=/var/lib/mysql-replica01
[root@localhost bin]# ./mysqld --initialize-insecure --datadir=/var/lib/mysql-replica02

复制服务管理脚本

# ① 切换到MySQL源代码目录中的scripts目录
[root@localhost bin]# cd ~/mysql-5.7.22/scripts
# ② 复制脚本文件
[root@localhost scripts]# cp mysqld@.service /usr/lib/systemd/system

启动多实例服务

# ① 启动实例mysqld@replica01和mysqld@replica02
[root@localhost bin]# systemctl start mysqld@replica01
[root@localhost bin]# systemctl start mysqld@replica02
# ② 设置开机自动启动
[root@localhost bin]# systemctl enable mysqld@replica01
[root@localhost bin]# systemctl enable mysqld@replica02

查看监听的端口号

[root@localhost bin]# ss -tnlp | grep mysql
LISTEN   0   80   :::3306    :::*   users:(("mysqld",pid=2709,fd=21))
LISTEN   0   80   :::3307    :::*   users:(("mysqld",pid=2820,fd=21))
LISTEN   0   80   :::3308    :::*   users:(("mysqld",pid=10534,fd=21))

登录并设置密码

# ① 登录3307端口的mysqld@replica01实例
[root@localhost bin]# mysql -h127.0.0.1 -P3307 -uroot
# ② 设置密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> exit
# ③ 登录3308端口的mysqld@replica02实例
[root@localhost bin]# mysql -h127.0.0.1 -P3308 -uroot
# ④ 设置密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> exit

12.4主从复制

主从复制（Replication）：

从一台MySQL主服务器（Master）将数据复制到另一台

或多台MySQL从服务器（Salves）。

主从复制实现原理：

通过二进制日志实现，主服务器的二进制日志发送给从服务器执行，

使从服务器的数据与主服务器同步。

主从复制的优点：

使从服务器可以分担主服务器的压力，并且从服务器可以作为主服务器的备份，防止因其中一台服务器故障导致数据丢失。

搭建主从复制环境：

主服务器 mysqld@replica01

从服务器 mysqld@replica02

更改配置文件

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

配置 [mysqld@replica01]

log-bin=binlog
server-id=2

配置 [mysqld@replica02]

server-id=3

重启服务使配置生效

[root@localhost ~]# systemctl restart mysqld@replica01
[root@localhost ~]# systemctl restart mysqld@replica02

登录主服务器

root@localhost ~]# mysql -h127.0.0.1 -P3307 -uroot -p123456

创建用于从服务器访问的用户slave，密码123456

# ① 创建slave用户
mysql> CREATE USER 'slave'@'127.0.0.1' IDENTIFIED BY '123456';
# ② 为用户赋予REPLICATION SLAVE权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'127.0.0.1';

查看主服务器当前的二进制日志状态

mysql> SHOW MASTER STATUS;
+---------------+------------+--------------+------------------+-------------------+
| File          | Position   | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+------------+--------------+------------------+-------------------+
| binlog.000001 |        623 |              |                  |                   |
+---------------+------------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

将结果记下来，然后退出主服务器

mysql> exit

登录从服务器

[root@localhost ~]# mysql -h127.0.0.1 -P3308 -uroot -p123456

从服务器自动同步主服务器

# ① 指定主服务器的信息
mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3307,
    -> MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=623;
# ② 使用slave用户开始同步
mysql> START SLAVE USER='slave' PASSWORD='123456';

CHANGE MASTER TO：更改从服务器连接主服务器的参数。

MASTER_HOST：主服务器的主机。

MASTER_PORT：主服务器的端口号。

MASTER_LOG_FILE：主服务器的二进制日志文件。

MASTER_LOG_POS：主服务器的位置。

USER和PASSWORD：访问主服务器的用户名和密码。

查看从服务器的同步状态

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
       Slave_IO_State: Waiting for master to send event
          Master_Host: 127.0.0.1		# 主服务器的主机
          Master_User: slave			# 主服务器的用户名
          Master_Port: 3307			# 主服务器的端口号
        Connect_Retry: 60			# 连接失败重试秒数
      Master_Log_File: binlog.000001		# 主服务器二进制日志文件
  Read_Master_Log_Pos: 623			# 主服务器的日志读取位置
       Relay_Log_File: localhost-relay-bin.000002	# 从服务器的中继日志文件
        Relay_Log_Pos: 319			# 从服务器中继日志的位置
Relay_Master_Log_File: binlog.000001		# 主服务器的中继日志文件
     Slave_IO_Running: Yes			# 从服务器IO线程是否运行
    Slave_SQL_Running: Yes			# 从服务器SQL线程是否运行
 ︙（此处省略一些显示结果）
1 row in set (0.00 sec)

退出从服务器

mysql> exit

在主服务器中写入数据，测试从服务器是否同步

# ① 登录主服务器
[root@localhost ~]# mysql -h127.0.0.1 -P3307 -uroot -p123456
# ② 创建mydb数据库和test数据表，插入测试数据
mysql> CREATE DATABASE mydb;
mysql> CREATE TABLE mydb.test (id INT);
mysql> INSERT INTO mydb.test VALUES(1);
# ③ 退出主服务器
mysql> exit
# ④ 登录从服务器
[root@localhost ~]# mysql -h127.0.0.1 -P3308 -uroot -p123456
# ⑤ 在从服务器中读取数据
mysql> SELECT * FROM mydb.test \G
*************************** 1. row ***************************
id: 1
1 row in set (0.00 sec)

在从服务器中写入数据，主服务器将不会同步

# ① 在从服务器中写入数据
mysql> INSERT INTO mydb.test VALUES(2);
# ② 退出从服务器
mysql> exit
# ③ 登录主服务器
[root@localhost ~]# mysql -h127.0.0.1 -P3307 -uroot -p123456
# ④ 在主服务器中读取数据
mysql> SELECT * FROM mydb.test\G
*************************** 1. row ***************************
id: 1
1 row in set (0.00 sec)

敲代码的江北

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL入门第十二天（数据库配置与部署）

目录12.1MySQL配置文件（1）配置区段（2）基本配置（3）内存和优化配置（4）日志配置12.2数据备份与还原（1）数据备份（2）数据还原（3）二进制日志12.3多实例部署12.4主从复制12.1MySQL配置文件（1）配置区段MySQL配置文件my.cnf的区段：[mysqld]：MySQL服务的配置[client]：客户端的配置（2）基本配置MySQL服务端基本配置SQL模式默认字符集默认校对集默认存
复制链接

扫一扫