526互联

some basic operations on mysql8

发布时间 2023-04-25 00:52:07作者: HighHill

it's my first time using version 8. keep a note just for practice.

$ sudo docker images | grep mysql
mysql             latest    8189e588b0e8   6 days ago      564MB
$ sudo docker image inspect mysql:latest | grep -i version
                "GOSU_VERSION=1.16",
                "MYSQL_VERSION=8.0.33-1.el8",
                "MYSQL_SHELL_VERSION=8.0.33-1.el8"
        "DockerVersion": "20.10.23",
                "GOSU_VERSION=1.16",
                "MYSQL_VERSION=8.0.33-1.el8",
                "MYSQL_SHELL_VERSION=8.0.33-1.el8"
  • binlog
    binlog stores differently in version 8 with 5.6/5.7, which is /var/lib/mysql. here is a practice on binlog.

    • binlog_format

      • row

      one record to a modification of a row, e.g . if one update SQL infects 10 rows then there're 10 records in binlog. so the binlog is larger than that generated by statement. but more efficient when copying data from master to slave.
      it's the default value. And I prefer to it(once I encoutered a problem when using SBR, statement based replication, when a UUID generated on master, when copying the slave generates another value. it's a disaster).
      about the variable binlog_row_image, refer to official doc

      mysql> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   2994206 | No        |
| binlog.000002 |       201 | No        |
| binlog.000003 |       157 | No        |
+---------------+-----------+-----------+
mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.01 sec)

mysql> use test_db;
Database changed
mysql> CREATE TABLE Persons (
	->     PersonID int,
	->     LastName varchar(255),
	->     FirstName varchar(255),
	->     Address varchar(255),
	->     City varchar(255)
	-> );
Query OK, 0 rows affected (0.01 sec)

mysql> select * from Persons;
Empty set (0.01 sec)

mysql> insert into Persons values (1, 'a', 'b', 'c', 'd');
Query OK, 1 row affected (0.05 sec)

mysql> select * from Persons;
+----------+----------+-----------+---------+------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+---------+------+
|        1 | a        | b         | c       | d    |
+----------+----------+-----------+---------+------+
1 row in set (0.00 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   2994206 | No        |
| binlog.000002 |       201 | No        |
| binlog.000003 |       982 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> flush logs
	-> ;
Query OK, 0 rows affected (0.02 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   2994206 | No        |
| binlog.000002 |       201 | No        |
| binlog.000003 |      1026 | No        |
| binlog.000004 |       157 | No        |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)

      check binlog and we can find some records besides SQL, e.g.

      CREATE DATABASE test_db
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
### INSERT INTO `test_db`.`Persons`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3='b' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @4='c' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @5='d' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */


      mysql> select * from Persons;
+----------+----------+-----------+---------+------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+---------+------+
|        1 | a        | b         | c       | i    |
|        2 | e        | f         | g       | h    |
+----------+----------+-----------+---------+------+
2 rows in set (0.00 sec)

mysql> insert into Persons values (1, 'j', 'f', 'g', 'h');
Query OK, 1 row affected (0.01 sec)

mysql> update Persons set city='k' where PersonID=1;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   2994206 | No        |
| binlog.000002 |       201 | No        |
| binlog.000003 |      1026 | No        |
| binlog.000004 |       201 | No        |
| binlog.000005 |       875 | No        |
| binlog.000006 |       201 | No        |
| binlog.000007 |       844 | No        |
+---------------+-----------+-----------+
7 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.06 sec)

      check binlog, 2 records for update SQL

      ### UPDATE `test_db`.`Persons`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3='b' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @4='c' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @5='i' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3='b' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @4='c' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @5='k' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### UPDATE `test_db`.`Persons`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='j' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3='f' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @4='g' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @5='h' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='j' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @3='f' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @4='g' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @5='k' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
      • statement

      record a SQL in binlog
      save network IO when copying.

      mysql> set session binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   2994206 | No        |
| binlog.000002 |       201 | No        |
| binlog.000003 |      1026 | No        |
| binlog.000004 |       201 | No        |
| binlog.000005 |       157 | No        |
+---------------+-----------+-----------+
5 rows in set (0.00 sec)

mysql> select * from Persons;
+----------+----------+-----------+---------+------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+---------+------+
|        1 | a        | b         | c       | d    |
+----------+----------+-----------+---------+------+
1 row in set (0.00 sec)

mysql> insert into Persons values (2, 'e', 'f', 'g', 'h');
Query OK, 1 row affected (0.00 sec)

mysql> update Persons set city='i' where PersonID=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from Persons;
+----------+----------+-----------+---------+------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+---------+------+
|        1 | a        | b         | c       | i    |
|        2 | e        | f         | g       | h    |
+----------+----------+-----------+---------+------+
2 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   2994206 | No        |
| binlog.000002 |       201 | No        |
| binlog.000003 |      1026 | No        |
| binlog.000004 |       201 | No        |
| binlog.000005 |       875 | No        |
| binlog.000006 |       157 | No        |
+---------------+-----------+-----------+
6 rows in set (0.00 sec)

      we can find update, insert in binlog but not select.

      root@4b3059437cfa:/var/lib/mysql# mysqlbinlog binlog.000005 | grep insert
insert into Persons values (2, 'e', 'f', 'g', 'h')
root@4b3059437cfa:/var/lib/mysql# mysqlbinlog binlog.000005 | grep update
update Persons set city='i' where PersonID=1
root@4b3059437cfa:/var/lib/mysql# mysqlbinlog binlog.000005 | grep select
root@4b3059437cfa:/var/lib/mysql#

    • replication
      some key variables on replica(you can check all variables here)

      • log-bin
        previously named bin-log
      • server-id
        relay-log
        log-slave-update=on
        read-only=on
  • some tools
    • maxScale
      it provides features such as authentication, protocal, router(readwritesplit, readconnroute), monitor and filter and loging.