MySQL认识与操作一

备份的类型

完全备份
部分备份
增量备份
差异备份

备份的方式

热备份：数据库运行中进行备份，读写不受影响
温备份：数据库运行中进行备份，可读不可写
冷备份：数据库关闭后进行备份

yum安装MySQL

#配置好yum源，包括epel源
curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
yum -y install wget vim
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
#使用官方yum仓库
wget https://dev.mysql.com/get/mysql80-community-release-el7-6.noarch.rpm
yum -y install mysql80-community-release-el7-6.noarch.rpm
#生成yum源缓存
yum makecache
yum -y install yum-utils
#检查安装的yum源是否存在
yum repolist enabled | grep mysql
#默认enable  mysql80
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community
#安装MySQL服务
yum install -y  mysql-community-server

如果出现如下报错：
GPG key retrieval failed: [Errno 14] curl#37 - “Couldn’t open file /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022”
用下面的方法处理：
编辑文件/etc/yum.repos.d/mysql-community.repo，把gpgkey值修改为：
https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

grep ‘temporary password’ /var/log/mysqld.log
mysql -uroot -p’password’
alter user root@localhost identified by ‘new_password’;
flush privileges;

#获取初始密码,修改root密码
[root@study-test ~]# grep 'temporary password' /var/log/mysqld.log
2023-02-24T15:01:26.269496Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: d&>2:#oP>Riu
[root@study-test ~]#
[root@study-test ~]# mysql -uroot -p'd&>2:#oP>Riu'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.32

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> 
mysql> alter user root@localhost   identified  by  'Hamor@123';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> quit
Bye
[root@study-test ~]# mysql -uroot -p'Hamor@123'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> 

添加用户

grant 权限 on 数据库.* to 用户名@登录主机 identified by “密码”
如：
grant all privilege on 数据库.* to 用户名@登录主机 identified by “密码”

添加用户并授权：

mysql> create user 'test'@'%' identified by 'Test@123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'test'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 

增删改数据
INSERT INTO 表名 (字段名1, 字段名2, …) VALUES (值1, 值2, …);
UPDATE 表名 SET 字段名 1 = 值 1 , 字段名 2 = 值 2 , … [ WHERE 条件 ] ;
DELETE FROM 表名 [ WHERE 条件 ] ;

备份命令

主机地址：10.0.1.198
主操作库名：DB1
完全备份所有数据库
mysqldump -h 10.0.1.198 -uroot -p --all-databases > xxx.sql

完全备份数据库DB1 (包含创建的语句)：
mysqldump -h 10.0.1.198 -uroot -p DB1 > xxx.sql

完全备份数据库database(不包含创建的语句)：
mysqldump -h 10.0.1.198 -uroot -p --databases DB1 > xxx.sql

备份数据库database（只包含表结构）：
mysqldump -h10.0.1.198 -uroot -p -d database > xxx.sql