服務-mysql

szlhwei發表於2024-07-03

安裝
- - - - 範例：二進位制安裝
mysql基礎
- - - - 範例：mysql客戶端登入，執行指令碼
      - 範例：管理使用者、許可權，增改鎖
      - 範例：檢視預設字符集和排序規則
常用SQL
- - - - 範例：資料庫DDL： CREATE ， DROP ，ALTER
      - 範例：資料表DDL： CREATE ， DROP ，ALTER
      - 範例：DML：INSERT, DELETE, UPDATE
      - 範例：DQL單表查詢語句
      - 範例：DQL多表子查詢
      - 範例：DQL多表查詢union/join
      - 範例：sql指令碼注意事項
mysql常用功能
- - - - 範例：備份/還原前全域性鎖
      - 範例：表write、read鎖
      - 範例：使用事務ACID特性完成一系列操作
      - 範例：kill事務鎖
日誌管理
- 事務日誌transaction log
  - - - 範例：事務日誌及效能說明（redo undo）
      - 範例：檢視錯誤日誌（.err）
      - 範例：開啟通用日誌（記錄操作語句）
      - 範例：二進位制日誌配置（binlog備份）
備份和恢復
- - - - 範例：冷備份和恢復
      - 範例：mysqldump實現完全備份和恢復
      - 範例：利用binlog二進位制日誌增量備份和還原
主從複製
- - - - 範例：mariadb實現主從複製

安裝

範例：二進位制安裝

流程
1、檔案準備：
	相關包：libaio  numactl-libs  ncurses-compat-libs
	mysql包：https://dev.mysql.com/downloads/，找lts長期支援的。

2、伺服器環境準備
	建立組，建立使用者，指定id
	解壓縮mysql指定目錄，授權，建立軟連線
	建立data目錄
	修改環境變數，建立service

3、初始化
	資料庫生成


###################################################
##1、檔案準備
[root@rocky31 ~]$yum -y install libaio  numactl-libs  ncurses-compat-libs
##-O指定名稱，避免某些下載名稱過長或有亂碼
[root@rocky31 ~]$wget  -O  mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz  'https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz'

###################################################
##2、伺服器環境準備
##建立系統組、使用者，1000以內為系統組、使用者，確認801未用
[root@rocky31 ~]$grep 801 /etc/passwd
[root@rocky31 ~]$grep 801 /etc/group
##-g指定id，-r指定建立系統組、系統使用者
[root@rocky31 ~]$groupadd -g 801 -r mysql
##建立系統使用者，-r系統使用者，-u指定id，-g指定組，-s指定預設shell（nologin不登入），-d指定主目錄，-c描述，最後加使用者名稱稱
[root@rocky31 ~]$useradd -r -u 801 -g mysql -s /sbin/nologin -c "mysql" mysql

##MySQL官方推薦的標準安裝路徑/usr/local/mysql，建立軟連線，並授權
[root@rocky31 ~]$tar  xvf  mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz  -C  /usr/local
[root@rocky31 ~]$ln  -s  /usr/local/mysql-8.4.0-linux-glibc2.28-x86_64/  /usr/local/mysql
[root@rocky31 ~]$chown  -R  mysql.mysql  /usr/local/mysql/

##增加環境變數
[root@rocky31 ~]$echo  'PATH=/usr/local/mysql/bin:$PATH'  >  /etc/profile.d/mysql.sh
[root@rocky31 ~]$.  /etc/profile.d/mysql.sh

##增加資料目錄
[root@rocky31 ~]$cat  >  /etc/my.cnf  <<EOF
> [mysqld]
> datadir=/data/mysql
> EOF
[root@rocky31 ~]$mkdir /data/mysql -p
[root@rocky31 ~]$chown  -R  mysql.mysql  /data/mysql/

##增加service
[root@rocky31 ~]$cp  /usr/local/mysql/support-files/mysql.server    /etc/init.d/mysqld
[root@rocky31 ~]$chkconfig --add mysqld
[root@rocky31 ~]$systemctl cat mysqld.service | grep start
Description=LSB: start and stop MySQL
Restart=no
ExecStart=/etc/rc.d/init.d/mysqld start


####################################################
##初始化，--initialize-insecure不設定root密碼，指定mysql使用者，--datadir指定mysql目錄，執行完會看到初始化的檔案
[root@rocky31 ~]$mysqld  --initialize-insecure  --user=mysql  --datadir=/data/mysql

##啟動mysqld服務，可以使用journalctl -f -u實時重新整理日誌
[root@rocky31 ~]$systemctl start mysqld.service
[root@rocky31 ~]$journalctl -f -u mysqld
-- Logs begin at Mon 2024-07-01 19:47:09 CST. --
7月 02 10:37:31 rocky31 systemd[1]: Starting LSB: start and stop MySQL...
7月 02 10:37:31 rocky31 mysqld[3155]: Starting MySQL.
7月 02 10:37:31 rocky31 mysqld[3163]: Logging to '/data/mysql/rocky31.err'.
7月 02 10:37:34 rocky31 mysqld[3155]: ..
7月 02 10:37:34 rocky31 mysqld[3322]: [  確定  ]
7月 02 10:37:34 rocky31 systemd[1]: Started LSB: start and stop MySQL.

##登入成功，輸入exit或\q退出
[root@rocky31 ~]$mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.

##修改密碼後，需要-p輸入密碼才能登入，否則報錯，無-u預設當前使用者登入
[root@rocky31 ~]$mysqladmin -uroot password '123456'
[root@rocky31 ~]$mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@rocky31 ~]$mysql -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql基礎

範例：mysql客戶端登入，執行指令碼

##-h,  --host=          伺服器主機  ,預設為localhost
##-p,  --passowrd=  使用者密碼  ,建議使用-p,預設為空密碼
##-P,  --port=          伺服器埠
##-D,  --database=          指定預設資料庫
##-e      "SQL"    執行SQL命令    
##-V,  --version      顯示版本

[root@rocky31 ~]$mysql  -e  'show  databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

[root@rocky31 ~]$cat >show_db.sql<<EOF
> show  databases;
> EOF
[root@rocky31 ~]$mysql <show_db.sql
Database
information_schema
mysql
performance_schema
sys

範例：管理使用者、許可權，增改鎖

##為方便登入，先把密碼置為空
mysql> alter  user  root@'localhost'  identified  by  '';
Query OK, 0 rows affected (0.00 sec)

##檢視使用者，'USERNAME'@'HOST'
mysql> select  user,host  from  mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)


##檢視當前使用者
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

##建立wang使用者，能從10.0.0.*網段登入，密碼123456
mysql> create user 'wang'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

##授權，給root使用者授權create、option，否則可能無法給wang使用者全部許可權
mysql> GRANT CREATE USER, GRANT OPTION ON *.* TO 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

##重新整理許可權
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'wang'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

##修改使用者密碼
mysql> alter user 'wang'@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)

##重新命名使用者
mysql> rename user wang@'10.0.0.%' to wei;
Query OK, 0 rows affected (0.01 sec)

##授權賬號，給select,insert,update指定hellodb.students庫表
mysql> grant select,insert,update on hellodb.students to 'wang'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

##給hellodb庫所有許可權
mysql> grant all on hellodb.* to 'wang'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

##建立admin賬號，給admin所有許可權，with  grant  option且能給其它賬戶授權
mysql> create user 'admin'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant  all  on  *.*  to  admin@'%'  with  grant  option;
Query OK, 0 rows affected (0.01 sec)

##鎖賬號
mysql> ALTER USER 'wang'@'10.0.0.%' ACCOUNT LOCK;
Query OK, 0 rows affected (0.00 sec)

##解鎖賬號
mysql> alter user wei@'%' account unlock;
Query OK, 0 rows affected (0.00 sec)

##revoke取消許可權，不如用鎖定，避免許可權內容不知道
mysql> revoke all on *.* from 'wang'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

##show grants檢視許可權
mysql> SHOW  GRANTS  FOR  'wang'@'10.0.0.%'\G
*************************** 1. row ***************************
Grants for wang@10.0.0.%: GRANT USAGE ON *.* TO `wang`@`10.0.0.%`
1 row in set (0.00 sec)

範例：檢視預設字符集和排序規則

[root@rocky31 ~]$mysql -e "show  variables  like  'collation%';"
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
[root@rocky31 ~]$mysql -e "show  variables  like  'character%'  ;"
+--------------------------+---------------------------------------------------------------+
| Variable_name            | Value                                                         |
+--------------------------+---------------------------------------------------------------+
| character_set_client     | utf8mb4                                                       |
| character_set_connection | utf8mb4                                                       |
| character_set_database   | utf8mb4                                                       |
| character_set_filesystem | binary                                                        |
| character_set_results    | utf8mb4                                                       |
| character_set_server     | utf8mb4                                                       |
| character_set_system     | utf8mb3                                                       |
| character_sets_dir       | /usr/local/mysql-8.4.0-linux-glibc2.28-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------------+

##修改字符集和排序方式為utf8
[root@rocky31 ~]$sed -i.bak '/mysqld/acharacter-set-server=utf8mb4\ncollation-server=utf8mb4_general_ci' /etc/my.cnf

常用SQL

範例：資料庫DDL： CREATE ， DROP ，ALTER

##建立資料庫
mysql>  create  database  db1  character  set  'utf8'  collate  'utf8_bin';
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> show  create  database  db1\G
*************************** 1. row ***************************
       Database: db1
Create Database: CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_bin */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

##再次建立報錯
mysql> create  database  db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
mysql> show  warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1007
Message: Can't create database 'db1'; database exists
1 row in set (0.00 sec)

##使用條件if則不會報錯，也不會建立
mysql> create  database    if  not  exists  db1;
Query OK, 1 row affected, 1 warning (0.01 sec)

##修改資料庫
mysql> alter  database  db1  character  set  utf8  collate  utf8_bin;
Query OK, 1 row affected, 2 warnings (0.01 sec)

##刪除資料庫
mysql> drop  database  db1;
Query OK, 0 rows affected (0.01 sec)

##檢視資料庫
mysql> show  databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

範例：資料表DDL： CREATE ， DROP ，ALTER

##需要先建立資料庫，使用資料庫後才能建立表；
mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> use school;
Database changed

##表欄位有各種型別，AUTO_INCREMENT  PRIMARY  KEY自增主鍵，char字元，int整數，decimal小數，enum列舉；
mysql> CREATE  TABLE  student  (
    -> id  int  UNSIGNED  AUTO_INCREMENT  PRIMARY  KEY,  name  VARCHAR(20)  NOT  NULL,
    -> age  tinyint  UNSIGNED,
    -> height  DECIMAL(5,2),
    -> gender  ENUM('M','F')  default  'M'
    -> )ENGINE=InnoDB  AUTO_INCREMENT=10  DEFAULT  CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)

##表欄位timestamp時間型別，CURRENT_TIMESTAMP當前時間
mysql> create  table  testdata  (id  int  auto_increment  primary  key,date  timestamp  DEFAULT  CURRENT_TIMESTAMP  NOT  NULL,name  char(3));
Query OK, 0 rows affected (0.01 sec)

##使用查詢結果建立新表
mysql> create  table  userdata      select  user,host  from  mysql.user;
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

##建立相同表結構
mysql> create table student2 like student;
Query OK, 0 rows affected (0.01 sec)

##建立外來鍵foreign  key
mysql> create  table  school  (  id  int  primary  key  auto_increment,name  varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> create  table  teacher(id  int  primary  key  auto_increment,name  varchar(10),  school_id  int,foreign  key(school_id)  references  school(id));
Query OK, 0 rows affected (0.01 sec)

##刪除表
mysql> drop table if exists student2;
Query OK, 0 rows affected (0.01 sec)

##修改表名
mysql> ALTER  TABLE  student  RENAME  s1;
Query OK, 0 rows affected (0.01 sec)

##修改表結構，增加欄位
mysql> ALTER  TABLE  s1  ADD  gender2  ENUM('m','f');
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

範例：DML：INSERT, DELETE, UPDATE


mysql> desc students;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| StuID     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| Name      | varchar(50)      | NO   |     | NULL    |                |
| Age       | tinyint unsigned | NO   |     | NULL    |                |
| Gender    | enum('F','M')    | NO   |     | NULL    |                |
| ClassID   | tinyint unsigned | YES  |     | NULL    |                |
| TeacherID | int unsigned     | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

##全插入
mysql> insert  students  values(null,'wang',18,'M',2,2);
Query OK, 1 row affected (0.01 sec)

##部分插入
mysql> insert  students(name,age)values('zhang',20);
Query OK, 1 row affected (0.00 sec)

##更新資料
##注意一定要有限制條件，否則將修改所有行資料，可在配置中增加如下，即MySQL將禁止不帶WHERE子句的UPDATE和DELETE語句
##[mysqld]
##sql_safe_updates = 1
mysql> update students set name='wei' where StuID='27';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

##刪除資料
##儘量不刪除，新增一個deleted標記刪除
mysql> alter table students add is_del bool default false;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

##清空資料
mysql> truncate table students;
Query OK, 0 rows affected (0.04 sec)

範例：DQL單表查詢語句

##指定列，查詢不知道資料量必須加limit限制結果
mysql> select  stuid  學員ID,name  as  姓名  ,gender  性別      from  students limit 3;
+----------+-------------+--------+
| 學員ID   | 姓名        | 性別   |
+----------+-------------+--------+
|        1 | Shi Zhongyu | M      |
|        2 | Shi Potian  | M      |
|        3 | Xie Yanke   | M      |
+----------+-------------+--------+
3 rows in set (0.00 sec)

##ifnull函式判斷null時的顯示
mysql> select  stuid,name,ifnull(classID,  '無班級  ')  from  students  where  classid  is  null;
+-------+-------------+---------------------------------+
| stuid | name        | ifnull(classID,  '無班級  ')    |
+-------+-------------+---------------------------------+
|    24 | Xu Xian     | 無班級                          |
|    25 | Sun Dasheng | 無班級                          |
+-------+-------------+---------------------------------+
2 rows in set (0.00 sec)

##distinct去重
mysql> select  distinct  gender  from  students  ;
+--------+
| gender |
+--------+
| M      |
| F      |
+--------+
2 rows in set (0.00 sec)

##查詢平均年齡
mysql> select  sum(age)/count(*)  from  students  where  gender  ='F';
+-------------------+
| sum(age)/count(*) |
+-------------------+
|           19.0000 |
+-------------------+
1 row in set (0.00 sec)

##group by分組查詢
mysql> select  count(*),gender  from students group by gender ;
+----------+--------+
| count(*) | gender |
+----------+--------+
|       15 | M      |
|       10 | F      |
+----------+--------+
2 rows in set (0.00 sec)

##order by 【desc】 排序
mysql> select  *  from  students  order  by  age  desc  limit  3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |
|     6 | Shi Qing    |  46 | M      |       5 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

範例：DQL多表子查詢

##子查詢作為單個條件
mysql> SELECT  Name,Age  FROM  students  WHERE  Age<(SELECT  avg(Age)  FROM  teachers) limit 3;
+-------------+-----+
| Name        | Age |
+-------------+-----+
| Shi Zhongyu |  22 |
| Shi Potian  |  22 |
| Xie Yanke   |  53 |
+-------------+-----+
3 rows in set (0.00 sec)

##子查詢作為多個條件，使用in
mysql> SELECT  Name,Age  FROM  students  WHERE  Age  IN  (SELECT  Age  FROM  teachers);
+-------------+-----+
| Name        | Age |
+-------------+-----+
| Sun Dasheng |  77 |
+-------------+-----+
1 row in set (0.00 sec)

##用於from子句的子查詢
mysql> SELECT  s.ClassID,s.aage  FROM  (SELECT  ClassID,avg(Age)  AS  aage  FROM  students  WHERE  ClassID  IS  NOT  NULL  GROUP  BY  ClassID) AS  s  WHERE  s.aage>30;
+---------+---------+
| ClassID | aage    |
+---------+---------+
|       2 | 36.0000 |
|       5 | 46.0000 |
+---------+---------+
2 rows in set (0.00 sec)

範例：DQL多表查詢union/join

##聯合查詢union，合併預設去重，不需要去重需要union all
mysql> SELECT  Name,Age  FROM  students  UNION  SELECT  Name,Age  FROM  teachers limit 3;
+-------------+-----+
| Name        | Age |
+-------------+-----+
| Shi Zhongyu |  22 |
| Shi Potian  |  22 |
| Xie Yanke   |  53 |
+-------------+-----+
3 rows in set (0.00 sec)

##inner join，內連線，取交集
mysql> select * from students inner join teachers on
    -> students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

##left outer join，左外連線，左表為主，右表無則顯示null
mysql>  select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s left outer join
    -> teachers as t on s.teacherid=t.tid limit 8;
+-------+-------------+-----+-----------+------+---------------+------+
| stuid | name        | age | teacherid | tid  | name          | age  |
+-------+-------------+-----+-----------+------+---------------+------+
|     1 | Shi Zhongyu |  22 |         3 |    3 | Miejue Shitai |   77 |
|     2 | Shi Potian  |  22 |         7 | NULL | NULL          | NULL |
|     3 | Xie Yanke   |  53 |        16 | NULL | NULL          | NULL |
|     4 | Ding Dian   |  32 |         4 |    4 | Lin Chaoying  |   93 |
|     5 | Yu Yutong   |  26 |         1 |    1 | Song Jiang    |   45 |
|     6 | Shi Qing    |  46 |      NULL | NULL | NULL          | NULL |
|     7 | Xi Ren      |  19 |      NULL | NULL | NULL          | NULL |
|     8 | Lin Daiyu   |  17 |      NULL | NULL | NULL          | NULL |
+-------+-------------+-----+-----------+------+---------------+------+
8 rows in set (0.00 sec)

範例：sql指令碼注意事項

##更新資料注意一定要有限制條件，否則將修改所有行資料，可在配置中增加如下，即MySQL將禁止不帶WHERE子句的UPDATE和DELETE語句
[mysqld]
sql_safe_updates = 1

##儘量不刪除，新增一個deleted標記刪除
mysql> alter table students add is_del bool default false;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

##查詢不知道資料量必須加limit限制結果
mysql> select  stuid  學員ID,name  as  姓名  ,gender  性別      from  students limit 3;

##防止SQL隱碼攻擊，輸入敏感資訊不能有特殊符號、且要限制長度
如使用者輸入在使用者名稱輸入“admin';  --”或“admin';  #  ”，即使密碼隨便輸入，萬一資料庫裡有admin賬戶，則能登入  

##優先使用join替代子查詢
執行子查詢時，需要為內層查詢語句的查詢結果建立一個臨時表。然後外層查詢語句從臨時表  中查詢記錄。查詢完畢後，再撤銷這些臨時表。因此，子查詢的速度會受到一定的影響。如果查詢的資料量比較大，這種影響就會隨之增大。
可以使用連線(JOIN)查詢來替代子查詢。連線查詢不需要建立臨時表，其速度比子查詢要快，如果查詢中使用到索引的話，效能會更好。

##索引
對於like語句，以  %  或者  _    開頭的不會使用索引，以  %  結尾會使用索引

mysql常用功能

範例：備份/還原前全域性鎖

##關閉正在開啟的表（清除查詢快取），通常在備份前加全域性讀鎖
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> create database db2;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

##全域性鎖，插入/更新資料會等待，查詢不受影響
mysql> insert students(name,age) values('wei',30);

##解鎖後，插入成功
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

範例：表write、read鎖

##write鎖，當前會話能查詢、修改；其它會話一直等待；
mysql> lock  table  teachers  write;
Query OK, 0 rows affected (0.00 sec)
mysql> unlock tables ;
Query OK, 0 rows affected (0.00 sec)

##read鎖，當前會話只能查詢；其它會話也能查詢；
mysql> lock  table  teachers  read;
Query OK, 0 rows affected (0.00 sec)
mysql> update teachers set age=30 where tid=1;
ERROR 1099 (HY000): Table 'teachers' was locked with a READ lock and can't be updated
mysql> unlock tables ;
Query OK, 0 rows affected (0.00 sec)

範例：使用事務ACID特性完成一系列操作

##為了保證一系列操作為一個整體操作，使用begin開始，rollback回滾，commit提交；
##rollback不能撤銷truncate、drop等DDL操作，只能撤銷DML；
##事務一次性提交，可減少磁碟I/O，提高速度；
##只有在當前會話commit後，其它會話查詢結果才看到最新值；不怕沒修改完斷網；
##Mysql預設隔離級別：可重複讀

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

mysql>  update  teachers  set  age=20  where  tid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

範例：kill事務鎖

##會話1，開啟begin，不結束
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update  teachers  set  age=50  where  tid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

##會話2，一直等待
mysql> update  teachers  set  age=70  where  tid=1;

##會話3，檢視鎖
mysql> SELECT  trx_mysql_thread_id,trx_state  FROM  information_schema.INNODB_TRX;
+---------------------+-----------+
| trx_mysql_thread_id | trx_state |
+---------------------+-----------+
|                  30 | RUNNING   |
+---------------------+-----------+
1 row in set (0.00 sec)
##檢視執行緒詳細資訊，確認是否可以kill
mysql> show processlist;
+----+-----------------+-----------+---------+---------+-------+------------------------+---------------------------------------------+
| Id | User            | Host      | db      | Command | Time  | State                  | Info                                        |
+----+-----------------+-----------+---------+---------+-------+------------------------+---------------------------------------------+
|  5 | event_scheduler | localhost | NULL    | Daemon  | 11700 | Waiting on empty queue | NULL                                        |
| 30 | root            | localhost | hellodb | Sleep   |   299 |                        | NULL                                        |
| 39 | root            | localhost | hellodb | Query   |    11 | updating               | update  teachers  set  age=70  where  tid=1 |
| 44 | root            | localhost | NULL    | Query   |     0 | init                   | show processlist                            |
+----+-----------------+-----------+---------+---------+-------+------------------------+---------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

##kill掉後會話2執行成功，會話1再操作時會報斷開
mysql> kill 30;

##檢視事務超時時長
mysql>  show  global  variables  like  'innodb_lock_wait_timeout'  ;
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.01 sec)

日誌管理

事務日誌transaction log

範例：事務日誌及效能說明（redo undo）

事務日誌作用：
redo log：可以用來恢復未寫入data file的已成功事務更新的資料
undo log：可以用來在事務失敗時進行rollback

事務日誌效能：
0	提交--日誌緩衝區--每秒寫入到磁碟
1	預設值：提交--日誌緩衝區，同時寫入到磁碟
2	提交--os緩衝區--每秒寫入到磁碟

使用1場景：安全，但效率較低，磁碟I/O較多；
使用2場景：高併發業務，os崩潰比mysql崩潰機率低，效率也高；

修改和檢視事務日誌配置：
如需修改，在/etc/my.cnf中的[mysqld]增加
innodb_flush_log_at_trx_commit=1

##檢視預設值
mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

範例：檢視錯誤日誌（.err）


檢視資料目錄
[root@rocky31 ~]$grep datadir /etc/my.cnf
datadir=/data/mysql

檢視日誌路徑
mysql>  SHOW GLOBAL VARIABLES LIKE 'log_error';
+---------------+---------------+
| Variable_name | Value         |
+---------------+---------------+
| log_error     | ./rocky31.err |
+---------------+---------------+
1 row in set (0.01 sec)

tail -f實時重新整理
[root@rocky31 ~]$tail -f /data/mysql/rocky31.err

或者用linux自帶journalctl日誌-f實時重新整理，-u指定程式
[root@rocky31 ~]$journalctl -fu mysqld

範例：開啟通用日誌（記錄操作語句）

記錄對資料庫的通用操作，包括:錯誤的SQL語句，預設不啟用

開啟後重啟服務
[root@rocky31 ~]$grep general_log /etc/my.cnf
general_log=ON

確認是否開啟
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

檢視日誌路徑
mysql> select @@general_log_file;
+-------------------------+
| @@general_log_file      |
+-------------------------+
| /data/mysql/rocky31.log |
+-------------------------+
1 row in set (0.00 sec)

驗證
[root@rocky31 ~]$tail -f /data/mysql/rocky31.log
...
2024-07-02T10:26:54.190608Z	    9 Query	select user()
2024-07-02T10:27:14.498637Z	    9 Query	show databases
...

範例：二進位制日誌配置（binlog備份）

功能：透過"重放"日誌檔案中的事件來生成資料副本
注意：建議二進位制日誌和資料檔案分開存放

二進位制日誌記錄三種格式：
statement	記錄語句，日誌量較少
row			記錄資料，日誌量較大，可用於備份和恢復
mixed		讓系統自行判定該基於哪種方式進行

二進位制檔案構成：
binlog.000001	日誌檔案，如binlog.000004一直累加
binlog.index	索引檔案，記錄當前已有的二進位制日誌檔案列表

常用配置說明：
sql_log_bin=ON		##開啟二進位制日誌
log_bin=/PATH/BIN_LOG_FILE	##指定檔案位置
binlog_format=ROW		##按行記錄資料
sync_binlog=1		##啟動二進位制日誌即時同步磁碟功能
都可以使用show variables like檢視當前值
mysql> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

##生成新的二進位制檔案
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

備份和恢復

範例：冷備份和恢復

##說明：31為主伺服器；41為準備一套一樣的系統和mysql版本
##冷備份和恢復需要停服務

##31伺服器
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

##41伺服器
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

##31使用rsync -a完全同步，程式、資料、配置
[root@rocky31 ~]$systemctl stop mysqld.service
[root@rocky31 ~]$rsync -a /usr/local/mysql 10.0.0.41:/usr/local/
[root@rocky31 ~]$rsync -a /data/mysql 10.0.0.41:/data/
[root@rocky31 ~]$rsync -a /etc/my.cnf 10.0.0.41:/etc/

##41開啟mysqld服務，登入驗證
[root@rocky-41 ~]$systemctl start mysqld.service
[root@rocky-41 ~]$mysql
...
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| admin            | %         |
| wei              | %         |
| wang             | 10.0.0.%  |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
7 rows in set (0.01 sec)

範例：mysqldump實現完全備份和恢復

##31備份，41恢復

##開啟二進位制，最好和資料庫分割槽
[root@rocky31 ~]$cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
binlog_format=row
log_bin=/mysqlbin/

[root@rocky31 ~]$ll /mysqlbin/
總用量 16
-rw-r----- 1 mysql mysql 181 7月   2 19:31 mysql.000001
-rw-r----- 1 mysql mysql 712 7月   2 20:55 mysql.000002
-rw-r----- 1 mysql mysql 158 7月   2 20:55 mysql.000003
-rw-r----- 1 mysql mysql  69 7月   2 20:55 mysql.index

#############################################################
##mysqldump常用備份選項
##-u登入使用者；-p密碼；
##-A所有資料庫；-B: 後跟要備份的資料庫列表
##-F重新整理伺服器日誌
##--master-data=2表示以註釋形式顯示change master to語句；可能名稱需要修改為--source-data；備份還原為2，需要設定主從複製為1；
##--single-transaction開啟事務，保證備份時的資料一致性
##--default-character-set=utf8設定預設字符集
##--flush-privileges在備份之前重新整理許可權
##--events事件排程器；--routines儲存過程和函式；--triggers觸發器；
#############################################################

##全庫備份，重新整理日誌
[root@rocky31 ~]$mysqldump  -uroot  -p  -A  -F --master-data=1 --single-transaction --flush-privileges --default-character-set=utf8 > fullback.sql
##備份後二進位制日誌檔案新建了
[root@rocky31 ~]$ll /mysqlbin/
總用量 20
-rw-r----- 1 mysql mysql 181 7月   2 19:31 mysql.000001
-rw-r----- 1 mysql mysql 712 7月   2 20:55 mysql.000002
-rw-r----- 1 mysql mysql 201 7月   2 21:02 mysql.000003
-rw-r----- 1 mysql mysql 158 7月   2 21:02 mysql.000004
-rw-r----- 1 mysql mysql  92 7月   2 21:02 mysql.index

##複製到41
[root@rocky31 ~]$scp fullback.sql 10.0.0.41:

##41恢復時，最好先關閉二進位制日誌，恢復完再開啟日誌
##備份指令碼都是建立庫和插入資料，所以可以直接執行，有判斷，很方便
[root@rocky-41 ~]$mysql -uroot -p'123' -e'set sql_log_bin=off;'
[root@rocky-41 ~]$mysql -p <fullback.sql
[root@rocky-41 ~]$mysql -uroot -p'123' -e'set sql_log_bin=on;'

範例：利用binlog二進位制日誌增量備份和還原

##按照之前完全備份的基礎，31伺服器mysql有變動，41增量還原

##31重新整理日誌，確定上次全量備份後的新日誌檔案為mysql.000005
[root@rocky31 ~]$mysql -e'flush logs;'
[root@rocky31 ~]$ll /mysqlbin/
總用量 24
-rw-r----- 1 mysql mysql 181 7月   2 19:31 mysql.000001
-rw-r----- 1 mysql mysql 712 7月   2 20:55 mysql.000002
-rw-r----- 1 mysql mysql 201 7月   2 21:02 mysql.000003
-rw-r----- 1 mysql mysql 383 7月   2 21:34 mysql.000004
-rw-r----- 1 mysql mysql 158 7月   2 21:34 mysql.000005
-rw-r----- 1 mysql mysql 115 7月   2 21:34 mysql.index

##使用mysqlbinlog將日誌檔案生成sql指令碼傳送到41
[root@rocky31 ~]$mysqlbinlog /mysqlbin/mysql.000005 >new.sql
[root@rocky31 ~]$scp new.sql 10.0.0.41:

##41先關閉二進位制記錄，然後執行，完成增量還原
[root@rocky-41 ~]$mysql -e'set sql_log_bin=off;'
[root@rocky-41 ~]$mysql <new.sql
[root@rocky-41 ~]$mysql -e'set sql_log_bin=on;'

主從複製

範例：mariadb實現主從複製

##部分mysql版本使用master關鍵字會報錯，因此使用mariadb
##實現：32為主有資料，31為空資料庫，實現31從節點只讀，資料和32相同且同步

#########################################################
##32伺服器
##設定服務id，開啟二進位制日誌
[root@anolis-32 ~]$cat >>/etc/my.cnf<<EOF
> [mysqld]
> log_bin=/data/mysqlbin/mysql-bin
> server-id=32
> EOF

##建立目錄並授權，啟動資料庫
[root@anolis-32 ~]$mkdir /data/mysqlbin/ -p && chown mysql.mysql /data/mysqlbin
[root@anolis-32 ~]$systemctl start mariadb.service

##匯入部分資料做驗證
[root@anolis-32 ~]$mysql <hellodb_innodb.sql
[root@anolis-32 ~]$mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

##建立repluser使用者並授權，用於從節點連線同步資料
[root@anolis-32 ~]$mysql -e 'create user repluser@"10.0.0.%" identified by "123";'
[root@anolis-32 ~]$mysql -e 'grant replication slave on *.* to repluser@"10.0.0.%";'

##全備份，--master-data=1生成change master to語句，傳送給31
[root@anolis-32 ~]$mysqldump -A -F --master-data=1 --single-transaction > all.sql
[root@anolis-32 ~]$scp all.sql 10.0.0.31:

#######################################################
##31伺服器
##設定服務id，且為只讀模式
[root@anolis31 ~]$cat >>/etc/my.cnf<<EOF
> [mysqld]
> server-id=31
> read-only
> EOF

##將32傳送的sql指令碼新增主機號，使用者和密碼，如果埠有修改也可以增加
[root@anolis31 ~]$sed -i.bak "s/^CHANGE MASTER TO/\
> CHANGE MASTER TO master_host='10.0.0.32',\
> master_user='repluser',\
> master_password='123',/" all.sql

##執行指令碼後，完成資料複製，有主節點，能啟動從節點
[root@anolis31 ~]$mysql < all.sql

##啟動從節點，檢視從節點狀態，且資料庫也同步，後續資料也會同步
[root@anolis31 ~]$mysql -e 'start slave;'
[root@anolis31 ~]$mysql -e 'show slave status\G;' | grep "Master_Host\|Slave_IO_Running\|Slave_SQL_Running"
                   Master_Host: 10.0.0.32
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
[root@anolis31 ~]$mysql -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| db2                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

mysql註冊服務
2020-04-07
MySql
windows配置MySql服務
2016-11-06
WindowsMySql
OrbStack搭建Mysql服務
2024-04-08
ORBMySql
CentOS 下 MySQL 服務搭建
2022-04-15
CentOSMySql
Docker安裝Mysql服務
2018-10-24
DockerMySql
搭建node服務（二）：操作MySQL
2020-06-03
MySql
mysql怎麼連線服務
2021-09-11
MySql
mysql for linux 配置多服務
2014-10-25
MySqlLinux
Node.js 建立MySql服務
2024-10-22
Node.jsMySql
docker-執行mysql服務
2024-03-25
DockerMySql
【MySql】開機自動啟動mysql服務
2011-12-30
MySql
如何在cmd中停止mysql服務
2021-09-11
MySql
cmd中如何重啟mysql服務
2021-09-11
MySql
mysql服務找不到如何解決
2021-09-11
MySql
Grafana+Prometheus 監控 MySql服務
2018-08-13
GrafanaPrometheusMySql
神器：新手快速搭建MySQL服務
2018-08-10
MySql
Node.js 服務連線 MySQL
2018-10-25
Node.jsMySql
MySql雙主一從服務搭建
2020-10-28
MySql
Kubernetes部署叢集Mysql服務
2023-03-10
MySql
Docker學習之搭建MySql容器服務
2018-03-29
DockerMySql
MySQL菜鳥實錄（一）：MySQL服務安裝實戰
2021-02-08
MySql
[MySQL] “MySQL 服務無法啟動”原理及解決方法
2024-04-04
MySql
shell指令碼：監控MySQL服務是否正常
2021-09-09
指令碼MySql
MYSQL資料庫服務無法啟動
2020-11-02
MySql資料庫
Oracle、Mysql 專業技術服務（兼職）
2022-11-29
OracleMySql
<postfix郵件服務下mysql的升級>
2014-06-17
MySql
Ubuntu 下啟動/停止/重啟mysql服務
2013-10-12
UbuntuMySql
MySQL 啟動服務報錯解決方案
2015-11-08
MySql
MySQL服務名無效或者MySQL正在啟動 MySQL無法啟動
2020-03-06
MySql
寶塔皮膚下，如果mysql服務意外停止，如何定時檢測並恢復服務
2024-07-20
MySql
MySQL資料庫與Nacos搭建監控服務
2023-04-07
MySql資料庫
通過shell指令碼檢測MySQL服務資訊
2018-05-22
指令碼MySql
win10的mysql服務無法啟動
2018-04-13
Win10MySql
mysqld_multi搭建MySQL單機多例項服務
2015-10-26
MySql
MySQL服務開啟關閉及登陸- 2
2016-03-17
MySql
shell指令碼：監控MySQL服務是否正常薦
2015-10-15
指令碼MySql
MySQL安裝後在服務裡找不到或者服務啟動不起來解決方法
2020-09-29
MySql
Linux—一臺主機部署多版本mysql服務和mysql例項
2018-01-17
LinuxMySql

服務-mysql

安裝

範例：二進位制安裝

mysql基礎

範例：mysql客戶端登入，執行指令碼

範例：管理使用者、許可權，增改鎖

範例：檢視預設字符集和排序規則

常用SQL

範例：資料庫DDL： CREATE ， DROP ，ALTER

範例：資料表DDL： CREATE ， DROP ，ALTER

範例：DML：INSERT, DELETE, UPDATE

範例：DQL單表查詢語句

範例：DQL多表子查詢

範例：DQL多表查詢union/join

範例：sql指令碼注意事項

mysql常用功能

範例：備份/還原前全域性鎖

範例：表write、read鎖

範例：使用事務ACID特性完成一系列操作

範例：kill事務鎖

日誌管理

事務日誌transaction log

範例：事務日誌及效能說明（redo undo）

範例：檢視錯誤日誌（.err）

範例：開啟通用日誌（記錄操作語句）

範例：二進位制日誌配置（binlog備份）

備份和恢復

範例：冷備份和恢復

範例：mysqldump實現完全備份和恢復

範例：利用binlog二進位制日誌增量備份和還原

主從複製

範例：mariadb實現主從複製

相關文章