tech 

MySQL相关

TE 0 Comments

— 1、列出当前数据库服务器上有哪些数据库
SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA;

— 2、列出当前数据库服务器上，指定数据库里面有哪些数据表
SELECT TABLE_NAME,ENGINE,TABLE_ROWS,TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘iedu_core’;

— 3、列出指定表的字段
SELECT ORDINAL_POSITION,COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,IS_NULLABLE,COLUMN_DEFAULT,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,CHARACTER_SET_NAME,COLUMN_KEY,EXTRA,COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ‘iedu_core’ AND TABLE_NAME = ‘school’ ORDER BY ORDINAL_POSITION ASC;

— 4、 列出指定表的索引
SELECT CONSTRAINT_NAME,COLUMN_NAME,ORDINAL_POSITION FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = ‘iedu_core’ AND TABLE_NAME = ‘school’ AND CONSTRAINT_NAME != ‘PRIMARY’

— 5、 列出指定数据库中的所有存储过程
SELECT ROUTINE_NAME,ROUTINE_DEFINITION,ROUTINE_COMMENT FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = ‘iedu_core’ AND ROUTINE_TYPE = ‘PROCEDURE’;

— 6、列出存储过程的参数
SELECT ORDINAL_POSITION,PARAMETER_MODE,PARAMETER_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DTD_IDENTIFIER FROM information_schema.PARAMETERS WHERE SPECIFIC_SCHEMA = ‘iedu_core’ AND SPECIFIC_NAME = ‘user_login’ ORDER BY ORDINAL_POSITION ASC;

1.查询表的结构信息
desc wp_users;

show columns from wp_users;

describe wp_users;

‘Database changed
MySQL [wordpress]> describe wp_users;
+———————+———————+——+—–+———————+—————-+
| Field | Type | Null | Key | Default | Extra |
+———————+———————+——+—–+———————+—————-+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_login | varchar(60) | NO | MUL | | |
| user_pass | varchar(255) | NO | | | |
| user_nicename | varchar(50) | NO | MUL | | |
| user_email | varchar(100) | NO | MUL | | |
| user_url | varchar(100) | NO | | | |
| user_registered | datetime | NO | | 0000-00-00 00:00:00 | |
| user_activation_key | varchar(255) | NO | | | |
| user_status | int(11) | NO | | 0 | |
| display_name | varchar(250) | NO | | | |
+———————+———————+——+—–+———————+—————-+
10 rows in set (0.01 sec)

MySQL [wordpress]> show columns from wp_users;
+———————+———————+——+—–+———————+—————-+
| Field | Type | Null | Key | Default | Extra |
+———————+———————+——+—–+———————+—————-+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_login | varchar(60) | NO | MUL | | |
| user_pass | varchar(255) | NO | | | |
| user_nicename | varchar(50) | NO | MUL | | |
| user_email | varchar(100) | NO | MUL | | |
| user_url | varchar(100) | NO | | | |
| user_registered | datetime | NO | | 0000-00-00 00:00:00 | |
| user_activation_key | varchar(255) | NO | | | |
| user_status | int(11) | NO | | 0 | |
| display_name | varchar(250) | NO | | | |
+———————+———————+——+—–+———————+—————-+
10 rows in set (0.00 sec)

MySQL [wordpress]>
‘

2 查询表的列信息。
select * from
information_schema.columns
where table_name=’wp_users’;

3 查看库中所有的库

SELECT LOWER(schema_name) schema_name
FROM
information_schema.schemata
WHERE
schema_name NOT IN (
‘mysql’,
‘information_schema’,
‘test’
);

‘MySQL [wordpress]> SELECT LOWER(schema_name) schema_name
-> FROM
-> information_schema.schemata
-> WHERE
-> schema_name NOT IN (
-> ‘mysql’,
-> ‘information_schema’,
-> ‘test’
-> );
+——————–+
| schema_name |
+——————–+
| performance_schema |
| sys |
| wordpress |
+——————–+
3 rows in set (0.00 sec)

MySQL [wordpress]>
‘

4 查询某个库中所有的表

SELECT table_name, create_time updated_at, table_type, ENGINE, table_rows num_rows, table_comment, CEIL(data_length / 1024 / 1024) store_capacity
FROM
information_schema.TABLES
WHERE table_schema = ‘wordpress’ AND table_name NOT LIKE ‘tmp#_%’ ESCAPE ‘#’;

‘MySQL [wordpress]> SELECT table_name, create_time updated_at, table_type, ENGINE, table_rows num_rows, table_comment, CEIL(data_length / 1024 / 1024) store_capacity
-> FROM
-> information_schema.TABLES
-> WHERE table_schema = ‘wordpress’ AND table_name NOT LIKE ‘tmp#_%’ ESCAPE ‘#’;
+———————–+———————+————+——–+———-+—————+—————-+
| TABLE_NAME | updated_at | TABLE_TYPE | ENGINE | num_rows | TABLE_COMMENT | store_capacity |
+———————–+———————+————+——–+———-+—————+—————-+
| wp_commentmeta | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_comments | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_links | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_options | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 181 | | 3 |
| wp_postmeta | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 108 | | 1 |
| wp_posts | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 51 | | 14 |
| wp_term_relationships | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 10 | | 1 |
| wp_term_taxonomy | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 4 | | 1 |
| wp_termmeta | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_terms | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 4 | | 1 |
| wp_usermeta | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 21 | | 1 |
| wp_users | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wfblockediplog | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wfblocks7 | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wfconfig | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 248 | | 2 |
| wp_wfcrawlers | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 1 | | 1 |
| wp_wffilechanges | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wffilemods | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 3993 | | 2 |
| wp_wfhits | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 3 | | 1 |
| wp_wfhoover | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wfissues | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 11 | | 1 |
| wp_wfknownfilelist | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 3965 | | 1 |
| wp_wflivetraffichuman | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wflocs | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 150 | | 1 |
| wp_wflogins | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 590 | | 1 |
| wp_wfnotifications | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 2 | | 1 |
| wp_wfpendingissues | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wfreversecache | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 1 | | 1 |
| wp_wfsnipcache | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 16 | | 1 |
| wp_wfstatus | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 1083 | | 1 |
| wp_wftrafficrates | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
+———————–+———————+————+——–+———-+—————+—————-+
31 rows in set (0.00 sec)

‘

5 查看某一个库下某一个表的所有字段

SELECT
lower(column_name) column_name,
ordinal_position position,
column_default dafault_value,
substring(is_nullable, 1, 1) nullable,
column_type data_type,
column_comment,
character_maximum_length data_length,
numeric_precision data_precision,
numeric_scale data_scale
FROM
information_schema.COLUMNS
WHERE
table_schema = ‘wordpress’
AND table_name = ‘wp_users’;

‘MySQL [wordpress]> SELECT
-> lower(column_name) column_name,
-> ordinal_position position,
-> column_default dafault_value,
-> substring(is_nullable, 1, 1) nullable,
-> column_type data_type,
-> column_comment,
-> character_maximum_length data_length,
-> numeric_precision data_precision,
-> numeric_scale data_scale
-> FROM
-> information_schema.COLUMNS
-> WHERE
-> table_schema = ‘wordpress’
-> AND table_name = ‘wp_users’;
+———————+———-+———————+———-+———————+—————-+————-+—————-+————+
| column_name | position | dafault_value | nullable | data_type | COLUMN_COMMENT | data_length | data_precision | data_scale |
+———————+———-+———————+———-+———————+—————-+————-+—————-+————+
| display_name | 10 | | N | varchar(250) | | 250 | NULL | NULL |
| id | 1 | NULL | N | bigint(20) unsigned | | NULL | 20 | 0 |
| user_activation_key | 8 | | N | varchar(255) | | 255 | NULL | NULL |
| user_email | 5 | | N | varchar(100) | | 100 | NULL | NULL |
| user_login | 2 | | N | varchar(60) | | 60 | NULL | NULL |
| user_nicename | 4 | | N | varchar(50) | | 50 | NULL | NULL |
| user_pass | 3 | | N | varchar(255) | | 255 | NULL | NULL |
| user_registered | 7 | 0000-00-00 00:00:00 | N | datetime | | NULL | NULL | NULL |
| user_status | 9 | 0 | N | int(11) | | NULL | 10 | 0 |
| user_url | 6 | | N | varchar(100) | | 100 | NULL | NULL |
+———————+———-+———————+———-+———————+—————-+————-+—————-+————+
10 rows in set (0.00 sec)

MySQL [wordpress]>
MySQL [wordpress]>
‘

6 查看某一个库下某一张表的索引
SELECT DISTINCT
lower(index_name) index_name,
lower(index_type) type
FROM
information_schema.statistics
WHERE
table_schema = ‘wordpress’
AND table_name = ‘wp_users’;

‘
MySQL [(none)]> SELECT DISTINCT
-> lower(index_name) index_name,
-> lower(index_type) type
-> FROM
-> information_schema.statistics
-> WHERE
-> table_schema = ‘wordpress’
-> AND table_name = ‘wp_users’;
+—————-+——-+
| index_name | type |
+—————-+——-+
| primary | btree |
| user_email | btree |
| user_login_key | btree |
| user_nicename | btree |
+—————-+——-+
4 rows in set (0.01 sec)

MySQL [(none)]>
MySQL [(none)]>
MySQL [(none)]>
‘

7 查看某一个库下某一个表的注释

SELECT
table_comment comments
FROM
information_schema.TABLES
WHERE
table_schema = ‘wordpress’
AND table_name = ‘wp_users’;

8

1.查看索引

（1）单位是GB

SELECT CONCAT(ROUND(SUM(index_length)/(102410241024), 2), ‘ GB’) AS ‘Total Index Size’ FROM information_schema.TABLES WHERE table_schema LIKE ‘wordpress’;

‘MySQL [wordpress]> SELECT CONCAT(ROUND(SUM(index_length)/(102410241024), 2), ‘ GB’) AS ‘Total Index Size’ FROM information_schema.TABLES WHERE table_schema LIKE ‘wordpress’;
+——————+
| Total Index Size |
+——————+
| 0.00 GB |
+——————+
1 row in set (0.00 sec)
‘

（2）单位是MB

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ‘ MB’) AS ‘Total Index Size’ FROM information_schema.TABLES WHERE table_schema LIKE ‘wordpress’;

‘MySQL [wordpress]> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ‘ MB’) AS ‘Total Index Size’ FROM information_schema.TABLES WHERE table_schema LIKE ‘wordpress’;
+——————+
| Total Index Size |
+——————+
| 0.84 MB |
+——————+
1 row in set (0.00 sec)

MySQL [wordpress]> ‘

其中“database”为你所要查看的数据库

2.查看表空间

SELECT CONCAT(ROUND(SUM(data_length)/(102410241024), 2), ‘ GB’) AS ‘Total Data Size’
FROM information_schema.TABLES WHERE table_schema LIKE ‘wordpress’;

‘MySQL [(none)]> SELECT CONCAT(ROUND(SUM(data_length)/(102410241024), 2), ‘ GB’) AS ‘Total Data Size’
-> FROM information_schema.TABLES WHERE table_schema LIKE ‘wordpress’;
+—————–+
| Total Data Size |
+—————–+
| 0.02 GB |
+—————–+
1 row in set (0.01 sec)

MySQL [(none)]>
‘

3.查看数据库中所有表的信息

SELECT CONCAT(table_schema,’.’,table_name) AS ‘Table Name’,
CONCAT(ROUND(table_rows/1000000,2),’M’) AS ‘Number of Rows’,
CONCAT(ROUND(data_length/(102410241024),2),’G’) AS ‘Data Size’,
CONCAT(ROUND(index_length/(102410241024),2),’G’) AS ‘Index Size’ ,
CONCAT(ROUND((data_length+index_length)/(102410241024),2),’G’) AS’Total’FROM information_schema.TABLES WHERE table_schema LIKE ‘wordpress’;

‘MySQL [(none)]> SELECT CONCAT(table_schema,’.’,table_name) AS ‘Table Name’,
-> CONCAT(ROUND(table_rows/1000000,2),’M’) AS ‘Number of Rows’,
-> CONCAT(ROUND(data_length/(102410241024),2),’G’) AS ‘Data Size’,
-> CONCAT(ROUND(index_length/(102410241024),2),’G’) AS ‘Index Size’ ,
-> CONCAT(ROUND((data_length+index_length)/(102410241024),2),’G’) AS’Total’FROM information_schema.TABLES WHERE table_schema LIKE ‘wordpress’;
+———————————+—————-+———–+————+——-+
| Table Name | Number of Rows | Data Size | Index Size | Total |
+———————————+—————-+———–+————+——-+
| wordpress.wp_commentmeta | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_comments | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_links | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_options | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_postmeta | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_posts | 0.00M | 0.01G | 0.00G | 0.01G |
| wordpress.wp_term_relationships | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_term_taxonomy | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_termmeta | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_terms | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_usermeta | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_users | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfblockediplog | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfblocks7 | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfconfig | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfcrawlers | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wffilechanges | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wffilemods | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfhits | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfhoover | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfissues | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfknownfilelist | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wflivetraffichuman | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wflocs | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wflogins | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfnotifications | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfpendingissues | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfreversecache | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfsnipcache | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wfstatus | 0.00M | 0.00G | 0.00G | 0.00G |
| wordpress.wp_wftrafficrates | 0.00M | 0.00G | 0.00G | 0.00G |
+———————————+—————-+———–+————+——-+
31 rows in set (0.01 sec)

MySQL [(none)]>
MySQL [(none)]> ‘

4.查看表相关信息：

SELECT table_name, create_time updated_at, table_type, ENGINE, table_rows num_rows, table_comment, CEIL(data_length / 1024 / 1024) store_capacity
FROM
information_schema.TABLES
WHERE table_schema = ‘wordpress’ AND table_name NOT LIKE ‘tmp#_%’ ESCAPE ‘#’;

‘MySQL [(none)]> SELECT table_name, create_time updated_at, table_type, ENGINE, table_rows num_rows, table_comment, CEIL(data_length / 1024 / 1024) store_capacity
-> FROM
-> information_schema.TABLES
-> WHERE table_schema = ‘wordpress’ AND table_name NOT LIKE ‘tmp#_%’ ESCAPE ‘#’;
+———————–+———————+————+——–+———-+—————+—————-+
| TABLE_NAME | updated_at | TABLE_TYPE | ENGINE | num_rows | TABLE_COMMENT | store_capacity |
+———————–+———————+————+——–+———-+—————+—————-+
| wp_commentmeta | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_comments | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_links | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_options | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 181 | | 3 |
| wp_postmeta | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 108 | | 1 |
| wp_posts | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 51 | | 14 |
| wp_term_relationships | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 10 | | 1 |
| wp_term_taxonomy | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 4 | | 1 |
| wp_termmeta | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_terms | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 4 | | 1 |
| wp_usermeta | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 21 | | 1 |
| wp_users | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wfblockediplog | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wfblocks7 | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wfconfig | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 248 | | 2 |
| wp_wfcrawlers | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 1 | | 1 |
| wp_wffilechanges | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wffilemods | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 3993 | | 2 |
| wp_wfhits | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 3 | | 1 |
| wp_wfhoover | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wfissues | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 11 | | 1 |
| wp_wfknownfilelist | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 3965 | | 1 |
| wp_wflivetraffichuman | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wflocs | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 150 | | 1 |
| wp_wflogins | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 590 | | 1 |
| wp_wfnotifications | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 2 | | 1 |
| wp_wfpendingissues | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
| wp_wfreversecache | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 1 | | 1 |
| wp_wfsnipcache | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 16 | | 1 |
| wp_wfstatus | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 1083 | | 1 |
| wp_wftrafficrates | 2019-04-26 01:40:26 | BASE TABLE | InnoDB | 0 | | 1 |
+———————–+———————+————+——–+———-+—————+—————-+
31 rows in set (0.01 sec)

MySQL [(none)]>
‘

5查看表索引

SELECT DISTINCT
lower(index_name) index_name,
lower(index_type) type
FROM
information_schema.statistics
WHERE
table_schema = ‘wordpress’
AND table_name = ‘wp_users’;

‘MySQL [(none)]> SELECT DISTINCT
-> lower(index_name) index_name,
-> lower(index_type) type
-> FROM
-> information_schema.statistics
-> WHERE
-> table_schema = ‘wordpress’
-> AND table_name = ‘wp_users’;
+—————-+——-+
| index_name | type |
+—————-+——-+
| primary | btree |
| user_email | btree |
| user_login_key | btree |
| user_nicename | btree |
+—————-+——-+
4 rows in set (0.00 sec)

MySQL [(none)]>
‘

查询数据库中所有表的自增主键：

SELECT
t.TABLE_NAME,
c.COLUMN_NAME,
ts.AUTO_INCREMENT
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
information_schema.TABLES AS ts,
information_schema.KEY_COLUMN_USAGE AS c
WHERE
t.TABLE_NAME = ts.TABLE_NAME
AND ts.TABLE_NAME = c.TABLE_NAME
— AND t.TABLE_SCHEMA = wordpress
AND t.CONSTRAINT_TYPE = ‘PRIMARY KEY’
ORDER BY ts.AUTO_INCREMENT DESC;

‘MySQL [wordpress]> SELECT
-> t.TABLE_NAME,
-> c.COLUMN_NAME,
-> ts.AUTO_INCREMENT
-> FROM
-> INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
-> information_schema.TABLES AS ts,
-> information_schema.KEY_COLUMN_USAGE AS c
-> WHERE
-> t.TABLE_NAME = ts.TABLE_NAME
-> AND ts.TABLE_NAME = c.TABLE_NAME
-> — AND t.TABLE_SCHEMA = wordpress
-> AND t.CONSTRAINT_TYPE = ‘PRIMARY KEY’
-> ORDER BY ts.AUTO_INCREMENT DESC;
+—————————————————–+———————–+—————-+
| TABLE_NAME | COLUMN_NAME | AUTO_INCREMENT |
+—————————————————–+———————–+—————-+
| wp_options | option_id | 4974 |
| wp_options | option_name | 4974 |
| wp_wfstatus | id | 4617 |
| wp_wfknownfilelist | id | 4033 |
| time_zone | Time_zone_id | 1821 |
| wp_wfhits | id | 684 |
| wp_wflogins | id | 652 |
| wp_postmeta | meta_id | 216 |
| wp_posts | ID | 93 |
| wp_wfissues | id | 45 |
| wp_usermeta | umeta_id | 25 |
| wp_wfsnipcache | id | 17 |
| wp_term_taxonomy | term_taxonomy_id | 5 |
| wp_term_taxonomy | term_id | 5 |
| wp_term_taxonomy | taxonomy | 5 |
| wp_terms | term_id | 5 |
| wp_comments | comment_ID | 3 |
| wp_users | ID | 2 |
| wp_commentmeta | meta_id | 1 |
| wp_links | link_id | 1 |
| wp_wfhoover | id | 1 |
| wp_wfpendingissues | id | 1 |
| component | component_id | 1 |
| wp_wfblocks7 | id | 1 |
| wp_termmeta | meta_id | 1 |
| innodb_index_stats | stat_name | NULL |
| wp_term_relationships | object_id | NULL |
| wp_wffilechanges | filenameHash | NULL |
| wp_wflocs | IP | NULL |
| wp_wftrafficrates | IP | NULL |
| events_waits_summary_by_thread_by_event_name | THREAD_ID | NULL |
| rwlock_instances | OBJECT_INSTANCE_BEGIN | NULL |
| events_stages_current | THREAD_ID | NULL |
| events_statements_current | EVENT_ID | NULL |
| events_statements_summary_by_program | OBJECT_NAME | NULL |
| events_transactions_summary_global_by_event_name | EVENT_NAME | NULL |
| memory_summary_global_by_event_name | EVENT_NAME | NULL |
| replication_connection_status | CHANNEL_NAME | NULL |
| prepared_statements_instances | OWNER_EVENT_ID | NULL |
| variables_by_thread | VARIABLE_NAME | NULL |
| default_roles | DEFAULT_ROLE_USER | NULL |
| password_history | Host | NULL |
| user | User | NULL |
| columns_priv | Table_name | NULL |
| proxies_priv | User | NULL |
| servers | Server_name | NULL |
| help_keyword | name | NULL |
| slave_relay_log_info | Channel_name | NULL |
| engine_cost | engine_name | NULL |
| wp_term_relationships | term_taxonomy_id | NULL |
| wp_wfblockediplog | IP | NULL |
| wp_wffilemods | filenameMD5 | NULL |
| wp_wftrafficrates | hitType | NULL |
| events_waits_summary_by_thread_by_event_name | EVENT_NAME | NULL |
| setup_actors | HOST | NULL |
| events_stages_current | EVENT_ID | NULL |
| events_statements_history | THREAD_ID | NULL |
| events_statements_histogram_global | BUCKET_NUMBER | NULL |
| socket_instances | OBJECT_INSTANCE_BEGIN | NULL |
| memory_summary_by_thread_by_event_name | THREAD_ID | NULL |
| replication_applier_configuration | CHANNEL_NAME | NULL |
| user_variables_by_thread | THREAD_ID | NULL |
| global_variables | VARIABLE_NAME | NULL |
| role_edges | FROM_HOST | NULL |
| password_history | User | NULL |
| tables_priv | Host | NULL |
| columns_priv | Column_name | NULL |
| proxies_priv | Proxied_host | NULL |
| help_topic | help_topic_id | NULL |
| time_zone_name | Name | NULL |
| slave_master_info | Channel_name | NULL |
| engine_cost | device_type | NULL |
| wp_wfblockediplog | unixday | NULL |
| wp_wfnotifications | id | NULL |
| cond_instances | OBJECT_INSTANCE_BEGIN | NULL |
| events_waits_summary_global_by_event_name | EVENT_NAME | NULL |
| setup_actors | USER | NULL |
| events_stages_history | THREAD_ID | NULL |
| events_statements_history | EVENT_ID | NULL |
| events_transactions_current | THREAD_ID | NULL |
| socket_summary_by_instance | OBJECT_INSTANCE_BEGIN | NULL |
| memory_summary_by_thread_by_event_name | EVENT_NAME | NULL |
| replication_applier_status | CHANNEL_NAME | NULL |
| user_variables_by_thread | VARIABLE_NAME | NULL |
| session_variables | VARIABLE_NAME | NULL |
| role_edges | FROM_USER | NULL |
| password_history | Password_timestamp | NULL |
| tables_priv | Db | NULL |
| procs_priv | Host | NULL |
| proxies_priv | Proxied_user | NULL |
| help_topic | name | NULL |
| slave_worker_info | Channel_name | NULL |
| sys_config | variable | NULL |
| innodb_table_stats | database_name | NULL |
| wp_wfblockediplog | blockType | NULL |
| events_waits_current | THREAD_ID | NULL |
| file_instances | FILE_NAME | NULL |
| setup_actors | ROLE | NULL |
| events_stages_history | EVENT_ID | NULL |
| events_statements_summary_by_thread_by_event_name | THREAD_ID | NULL |
| events_transactions_current | EVENT_ID | NULL |
| socket_summary_by_event_name | EVENT_NAME | NULL |
| table_handles | OBJECT_INSTANCE_BEGIN | NULL |
| replication_applier_status_by_coordinator | CHANNEL_NAME | NULL |
| status_by_thread | THREAD_ID | NULL |
| persisted_variables | VARIABLE_NAME | NULL |
| role_edges | TO_HOST | NULL |
| tables_priv | User | NULL |
| procs_priv | Db | NULL |
| ndb_binlog_index | epoch | NULL |
| help_category | help_category_id | NULL |
| time_zone_transition | Time_zone_id | NULL |
| slave_worker_info | Id | NULL |
| innodb_table_stats | table_name | NULL |
| wp_wfreversecache | IP | NULL |
| events_waits_current | EVENT_ID | NULL |
| file_summary_by_event_name | EVENT_NAME | NULL |
| setup_consumers | NAME | NULL |
| events_stages_summary_by_thread_by_event_name | THREAD_ID | NULL |
| events_statements_summary_by_thread_by_event_name | EVENT_NAME | NULL |
| events_transactions_history | THREAD_ID | NULL |
| session_connect_attrs | PROCESSLIST_ID | NULL |
| metadata_locks | OBJECT_INSTANCE_BEGIN | NULL |
| replication_applier_status_by_worker | CHANNEL_NAME | NULL |
| status_by_thread | VARIABLE_NAME | NULL |
| user_defined_functions | UDF_NAME | NULL |
| role_edges | TO_USER | NULL |
| db | Host | NULL |
| tables_priv | Table_name | NULL |
| procs_priv | User | NULL |
| ndb_binlog_index | orig_server_id | NULL |
| help_category | name | NULL |
| time_zone_transition | Transition_time | NULL |
| gtid_executed | source_uuid | NULL |
| innodb_index_stats | database_name | NULL |
| wp_wfconfig | name | NULL |
| events_waits_history | THREAD_ID | NULL |
| file_summary_by_instance | OBJECT_INSTANCE_BEGIN | NULL |
| setup_instruments | NAME | NULL |
| events_stages_summary_by_thread_by_event_name | EVENT_NAME | NULL |
| events_statements_summary_global_by_event_name | EVENT_NAME | NULL |
| events_transactions_history | EVENT_ID | NULL |
| session_connect_attrs | ATTR_NAME | NULL |
| data_locks | ENGINE_LOCK_ID | NULL |
| replication_applier_status_by_worker | WORKER_ID | NULL |
| global_status | VARIABLE_NAME | NULL |
| default_roles | HOST | NULL |
| global_grants | USER | NULL |
| db | Db | NULL |
| columns_priv | Host | NULL |
| procs_priv | Routine_name | NULL |
| ndb_binlog_index | orig_epoch | NULL |
| help_relation | help_keyword_id | NULL |
| time_zone_transition_type | Time_zone_id | NULL |
| gtid_executed | interval_start | NULL |
| innodb_index_stats | table_name | NULL |
| wp_wfcrawlers | IP | NULL |
| wp_wflivetraffichuman | IP | NULL |
| events_waits_history | EVENT_ID | NULL |
| host_cache | IP | NULL |
| setup_threads | NAME | NULL |
| events_stages_summary_global_by_event_name | EVENT_NAME | NULL |
| events_statements_summary_by_program | OBJECT_TYPE | NULL |
| events_transactions_summary_by_thread_by_event_name | THREAD_ID | NULL |
| session_account_connect_attrs | PROCESSLIST_ID | NULL |
| data_locks | ENGINE | NULL |
| prepared_statements_instances | OBJECT_INSTANCE_BEGIN | NULL |
| session_status | VARIABLE_NAME | NULL |
| default_roles | USER | NULL |
| global_grants | HOST | NULL |
| db | User | NULL |
| columns_priv | Db | NULL |
| procs_priv | Routine_type | NULL |
| func | name | NULL |
| help_relation | help_topic_id | NULL |
| time_zone_transition_type | Transition_type_id | NULL |
| server_cost | cost_name | NULL |
| innodb_index_stats | index_name | NULL |
| wp_wfcrawlers | patternSig | NULL |
| wp_wflivetraffichuman | identifier | NULL |
| wp_wftrafficrates | eMin | NULL |
| events_waits_summary_by_instance | OBJECT_INSTANCE_BEGIN | NULL |
| mutex_instances | OBJECT_INSTANCE_BEGIN | NULL |
| threads | THREAD_ID | NULL |
| events_statements_current | THREAD_ID | NULL |
| events_statements_summary_by_program | OBJECT_SCHEMA | NULL |
| events_transactions_summary_by_thread_by_event_name | EVENT_NAME | NULL |
| session_account_connect_attrs | ATTR_NAME | NULL |
| replication_connection_configuration | CHANNEL_NAME | NULL |
| prepared_statements_instances | OWNER_THREAD_ID | NULL |
| variables_by_thread | THREAD_ID | NULL |
| default_roles | DEFAULT_ROLE_HOST | NULL |
| global_grants | PRIV | NULL |
| user | Host | NULL |
| columns_priv | User | NULL |
| proxies_priv | Host | NULL |
| plugin | name | NULL |
| help_keyword | help_keyword_id | NULL |
| time_zone_leap_second | Transition_time | NULL |
| engine_cost | cost_name | NULL |
+—————————————————–+———————–+—————-+
200 rows in set (0.01 sec)

MySQL [wordpress]> ‘

查询数据库中所有表的主键及数量(太多，limit到100）：

SELECT
t.TABLE_NAME,
t.CONSTRAINT_TYPE,
c.COLUMN_NAME,
c.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c,
information_schema.TABLES AS ts
WHERE
t.TABLE_NAME = c.TABLE_NAME
— AND t.TABLE_SCHEMA = wordpress
AND t.CONSTRAINT_TYPE = ‘PRIMARY KEY’
limit 100;

查询没有主键的表

select table_schema,table_name from information_schema.tables
where (table_schema,table_name) not in(
    select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI'    
)
and table_schema not in (
    'sys','mysql','information_schema','performance_schema'
);

SELECT
t.TABLE_NAME,
t.CONSTRAINT_TYPE,
c.COLUMN_NAME,
c.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c,
information_schema.TABLES AS ts
WHERE
t.TABLE_NAME = c.TABLE_NAME
— AND t.TABLE_SCHEMA = testdb
AND t.CONSTRAINT_TYPE = ‘PRIMARY KEY’
limit 100;

创建表后，可以在修改数据表时添加主键约束，语法规则为：

ALTER TABLE <数据表名> ADD PRIMARY KEY(<列名>);

alter table table_test add primary key(id);

先删除主键
alter table table_test drop primary key;
然后再增加主键
alter table table_test add primary key(id);
注:在增加主键之前,必须先把反复的id删除掉。

set global binlog_expire_logs_seconds=604800;

mysql> ALTER TABLE tb_emp3

-> ADD PRIMARY KEY(id);

Query OK, 0 rows affected (0.94 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC tb_emp3;

+——–+————-+——+—–+———+——-+

| Field | Type | Null | Key | Default | Extra |

+——–+————-+——+—–+———+——-+

| id | int(11) | NO | PRI | NULL | |

| name | varchar(30) | YES | | NULL | |

| deptId | int(11) | YES | | NULL | |

| salary | float | YES | | NULL | |

+——–+————-+——+—–+———+——-+

4 rows in set (0.12 sec)

#

在本地数据库中创建迁移账号。
CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;
参数说明：

username：要创建的账号
host：指定该账号登录数据库的主机。如果是本地用户可以使用 localhost，如果想让该用户从任意主机登录，可以使用通配符 %
password：该账号的登录密码
例：要创建账号为 William，密码为 Changme123 的账号从任意主机登录本地数据库，命令如下：

CREATE USER ‘William’@’%’ IDENTIFIED BY ‘Changme123’;
在本地数据库中给迁移账号授权，本地数据库中迁移账号的权限要求请参见上表。
GRANT privileges ON databasename.tablename TO ‘username’@’host’ WITH GRANT OPTION;
参数说明：

privileges：该账号的操作权限，如 SELECT、INSERT、UPDATE 等。如果要授权该账号所有权限，则使用 ALL
databasename：数据库名。如果要授权该账号所有的数据库权限，则使用通配符 *
tablename：表名。如果要授权该账号所有的表权限，则使用通配符 *
username：要授权的账号名
host：授权登录数据库的主机名。如果是本地用户可以使用 localhost，如果想让该用户从任意主机登录，可以使用通配符 %
WITH GRANT OPTION：授权该账号能使用GRANT命令，该参数为可选
例：授权账号 William 对所有数据库和表的所有权限，并可以从任意主机登录本地数据库，命令如下：

GRANT ALL ON . TO ‘William’@’%’;

开启本地数据库的 binlog。
使用如下命令查询是否开启了binlog。

set global binlog_expire_logs_seconds=604800;
如果查询结果为 log_bin=OFF，那么本地数据库没有开启 binlog。为了使迁移过程中产生的增量数据能同步迁移，需要修改配置文件 my.cnf 中的如下参数。

log_bin=mysql_bin
binlog_format=row
server_id=大于 1 的整数
binlog_row_image=full //当本地 MySQL 版本大于 5.6 时，则需设置该项
修改完成后，重启 MySQL 进程。
$mysql_dir/bin/mysqladmin -u root -p shutdown
$mysql_dir/bin/safe_mysqld &

在正式迁移之前，需要先在本地数据库和 RDS 实例中创建迁移账号，并在 RDS 实例中创建要迁移的数据库，并将要迁移的数据库的读写权限授权给迁移账号。
不同的迁移类型需要不同的权限，如下表所示。

迁移类型 结构迁移 全量迁移 增量迁移
本地数据库 select select select,replication ,slave ,replication, client
RDS 实例 读写权限 读写权限 读写权限

##########sysbench

Debian/Ubuntu
apt -y install make automake libtool pkg-config libaio-dev
# For MySQL support
apt -y install libmysqlclient-dev libssl-dev
# For PostgreSQL support
apt -y install libpq-dev

yum -y install make automake libtool pkgconfig libaio-devel

# For MySQL support, replace with mysql-devel on RHEL/CentOS 5
yum -y install mariadb-devel openssl-devel
# For PostgreSQL support
yum -y install postgresql-devel

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

sudo yum -y install sysbench

mysql-cn-east-2-784619174cc84f73.rds.jdcloud.com -u zjz -p

sysbench –num-threads=32 –max-time=3600 –max-requests=999999999 –test= oltp.lua –oltp-table-size=10000000 –oltp-tables-count=64 –db-driver=mysql –mysql-table-engine=innodb –mysql-host= mysql-cn-east-2-784619174cc84f73.rds.jdcloud.com –mysql-port=3306 –mysql-user= zjz –mysql-password= Beichen1 prepare

###############migration needs

1、设置源端每个实例Binlog保留时间为7天
set global binlog_expire_logs_seconds=604800;

2、确认源端实例空间足够大，Binlog日志在7天内不被清除

3、AWS源数据库账号权限为只读，京东云RDS数据库账号权限为读写

4、源端数据库表必须有主键

5、迁移期间，源库数据库不允许扩容

6、迁移期间，源端和目标端数据库不允许进行DDL操作

7、源端和目标端数据库编码一致

8、迁移实例外网IP地址加入AWS源库和京东云RDS数据库白名单

先删除主键
alter table table_test drop primary key;
然后再增加主键
alter table table_test add primary key(id);
注:在增加主键之前,必须先把反复的id删除掉。

set global binlog_expire_logs_seconds=604800;

mysql> select * from wp_options where option_name like ‘%URL%’;
+———–+——————————–+————————+———-+
| option_id | option_name | option_value | autoload |
+———–+——————————–+————————+———-+
| 1 | siteurl | http://114.115.213.156 | yes |
| 14 | mailserver_url | mail.example.com | yes |
| 57 | upload_url_path | | yes |
| 419 | jetpack_last_connect_url_check | 1547161626 | no |
+———–+——————————–+————————+———-+
4 rows in set (0.00 sec)

创建用户

create user ‘wordpress’@’localhost’ identified by ‘hw2wordpress’;

create user ‘test1’@’localhost’ identified by ‘123’;

flush privileges;刷新权限

create user ‘wordpress’@’%’ identified by ‘hw2wordpress’;

其中localhost指本地才可连接

可以将其换成%指任意ip都能连接

也可以指定ip连接

修改密码

Alter user ‘test1’@’localhost’ identified by ‘新密码’;

flush privileges;

授权

grant all privileges on . to ‘wordpress’@’localhost’ with grant option;

grant all privileges on . to ‘wordpress’@’%’ with grant option;

with gran option表示该用户可给其它用户赋予权限，但不可能超过该用户已有的权限

比如a用户有select,insert权限，也可给其它用户赋权，但它不可能给其它用户赋delete权限，除了select,insert以外的都不能

这句话可加可不加，视情况而定。

all privileges 可换成select,update,insert,delete,drop,create等操作

如：grant select,insert,update,delete on . to ‘test1’@’localhost’;

第一个*表示通配数据库，可指定新建用户只可操作的数据库

如：grant all privileges on 数据库.* to ‘test1’@’localhost’;

第二个*表示通配表，可指定新建用户只可操作的数据库下的某个表

如：grant all privileges on 数据库.指定表名 to ‘test1’@’localhost’;

查看用户授权信息

show grants for ‘test1’@’localhost’;

撤销权限

revoke all privileges on . from ‘test1’@’localhost’;

用户有什么权限就撤什么权限

删除用户

drop user ‘test1’@’localhost’;

mysql>
GRANT ALL PRIVILEGES ON . TO ‘wordpress’@’%’ IDENTIFIED BY ‘hw2wordpress’ WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON . TO ‘wordpress’@”%” IDENTIFIED BY ‘hw2wordpress’;
FLUSH PRIVILEGES;

8.0 远程拒绝连接

ALTER USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY ‘root’;

ALTER USER ‘wordpress’@’%’ IDENTIFIED WITH mysql_native_password BY ‘hw2wordpress’;
FLUSH PRIVILEGES;

修改配置文件：
增加 default_authentication_plugin = mysql_native_password

[root@r3 wordpress]$ more my.cnf

Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.

#

This program is free software; you can redistribute it and/or modify

it under the terms of the GNU General Public License as published by

the Free Software Foundation; version 2 of the License.

#

This program is distributed in the hope that it will be useful,

but WITHOUT ANY WARRANTY; without even the implied warranty of

MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

GNU General Public License for more details.

#

You should have received a copy of the GNU General Public License

along with this program; if not, write to the Free Software

Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

#

The MySQL Server configuration file.

#

For explanations see

http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0
default_authentication_plugin = mysql_native_password

Custom config should go here

!includedir /etc/mysql/conf.d/

[root@r3 wordpress]$

################调整IP 及URL

33 rows in set (0.00 sec)

MySQL [wordpress]> select * from wp_options where option_name like ‘%URL%’;
+———–+——————————–+————————+———-+
| option_id | option_name | option_value | autoload |
+———–+——————————–+————————+———-+
| 1 | siteurl | http://114.115.213.156 | yes |
| 14 | mailserver_url | mail.example.com | yes |
| 57 | upload_url_path | | yes |
| 419 | jetpack_last_connect_url_check | 1547161626 | no |
+———–+——————————–+————————+———-+
4 rows in set (0.00 sec)

MySQL [wordpress]> select * from wp_options where option_name like ‘%ip%’;
+———–+———————-+———————–+———-+
| option_id | option_name | option_value | autoload |
+———–+———————-+———————–+———-+
| 4 | blogdescription | 网聚学习的力量 | yes |
| 156 | can_compress_scripts | 1 | no |
+———–+———————-+———————–+———-+
2 rows in set (0.00 sec)

MySQL [wordpress]> select * from wp_options where option_name like ‘%172%’;
Empty set (0.00 sec)

MySQL [wordpress]>

word press 目录

[root@bj-02-dev _data]$ pwd
/var/lib/docker/volumes/wordpress_wordpress/_data
[root@bj-02-dev _data]$

[root@r3 _data]$ pwd
/var/lib/docker/volumes/wordpress_wordpress/_data
[root@r3 _data]$

MySQL的目录

[root@bj-02-dev _data]$ docker volume inspect wordpress_mysql
[
{
“Driver”: “local”,
“Labels”: {
“com.docker.compose.project”: “wordpress”,
“com.docker.compose.version”: “1.23.1”,
“com.docker.compose.volume”: “mysql”
},
“Mountpoint”: “/var/lib/docker/volumes/wordpress_mysql/_data”,
“Name”: “wordpress_mysql”,
“Options”: {},
“Scope”: “local”
}
]
[root@bj-02-dev _data]$

[root@bj-02-dev _data]$ ls /var/lib/docker/volumes/wordpress_mysql/_data
auto.cnf binlog.000015 ca-key.pem client-key.pem ibdata1 ibtmp1 mysql.ibd private_key.pem server-key.pem test undo_002
binlog.000013 binlog.000016 ca.pem gvido ib_logfile0 #innodb_temp mysql_upgrade_info public_key.pem stg02gvido test1 wordpress
binlog.000014 binlog.index client-cert.pem ib_buffer_pool ib_logfile1 mysql performance_schema server-cert.pem sys undo_001 wordpress20191023.sql

[root@bj-02-dev _data]$ du /var/lib/docker/volumes/wordpress_mysql/_data -h
24M /var/lib/docker/volumes/wordpress_mysql/_data/gvido
164K /var/lib/docker/volumes/wordpress_mysql/_data/#innodb_temp
4.0K /var/lib/docker/volumes/wordpress_mysql/_data/stg02gvido
12K /var/lib/docker/volumes/wordpress_mysql/_data/test
198M /var/lib/docker/volumes/wordpress_mysql/_data/wordpress
4.0K /var/lib/docker/volumes/wordpress_mysql/_data/test1
15M /var/lib/docker/volumes/wordpress_mysql/_data/mysql
1.4M /var/lib/docker/volumes/wordpress_mysql/_data/performance_schema
124K /var/lib/docker/volumes/wordpress_mysql/_data/sys
810M /var/lib/docker/volumes/wordpress_mysql/_data
[root@bj-02-dev _data]$

[root@r3 _data]$ docker volume inspect wordpress_mysql
[
{
“CreatedAt”: “2019-10-25T13:25:09+08:00”,
“Driver”: “local”,
“Labels”: {
“com.docker.compose.project”: “wordpress”,
“com.docker.compose.volume”: “mysql”
},
“Mountpoint”: “/var/lib/docker/volumes/wordpress_mysql/_data”,
“Name”: “wordpress_mysql”,
“Options”: null,
“Scope”: “local”
}
]
[root@r3 _data]$

[root@r3 _data]$ ls /var/lib/docker/volumes/wordpress_mysql/_data
auto.cnf binlog.000003 binlog.000006 binlog.000009 binlog.000012 binlog.000015 binlog.000018 ca.pem ib_buffer_pool ib_logfile1 mysql private_key.pem server-key.pem undo_002
binlog.000001 binlog.000004 binlog.000007 binlog.000010 binlog.000013 binlog.000016 binlog.index client-cert.pem ibdata1 ibtmp1 mysql.ibd public_key.pem sys wordpress
binlog.000002 binlog.000005 binlog.000008 binlog.000011 binlog.000014 binlog.000017 ca-key.pem client-key.pem ib_logfile0 #innodb_temp performance_schema server-cert.pem undo_001 wordpress.sql

[root@r3 _data]$ du -h /var/lib/docker/volumes/wordpress_mysql/_data
160K /var/lib/docker/volumes/wordpress_mysql/_data/#innodb_temp
28K /var/lib/docker/volumes/wordpress_mysql/_data/mysql
1.4M /var/lib/docker/volumes/wordpress_mysql/_data/performance_schema
80K /var/lib/docker/volumes/wordpress_mysql/_data/sys
35M /var/lib/docker/volumes/wordpress_mysql/_data/wordpress
242M /var/lib/docker/volumes/wordpress_mysql/_data
[root@r3 _data]$

1.mysql语法：

1 update 表名 set 指定字段 =replace(指定字段,’要替换的字符串’,’想要的字符串’) where 条件
2.测试语句：

1 UPDATE data_set
2 SET serial_number = REPLACE (serial_number, “XX”, “CS”)
3 WHERE
4 serial_number LIKE “XX%”
5 AND father_id = 40;
3.结果说明：

原来字段值 “XX0102”,执行后字段值 “CS0102” ;

mysql替换表的字段里面内容，如例子：

引用

mysql> select host,user from user where user=’user’;
+———————–+———-+
| host | user |
+———————–+———-+
| localhost.localdomain | testuser |
+———————–+———-+

update字段host的内容，把”main”改成”slave”,用REPLACE

mysql> update user set host=REPLACE(host,’main’,’slave’) where user=’user’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select host,user from user where user=’user’;
+————————+———-+
| host | user |
+————————+———-+
| localhost.localdoslave | testuser |
+————————+———-+

update wp_users set user_url=REPLACE(user_url,’116.198.163.75′,’114.67.202.148′)

mysql> select user_url from wp_users;
+———————–+
| user_url |
+———————–+
| http://116.198.163.75 |
+———————–+
1 row in set (0.01 sec)

mysql> update wp_users set user_url=REPLACE(user_url,’116.198.163.75′,’114.67.202.148′)
-> ;

update wp_posts set guid=REPLACE(guid,'116.198.163.75','114.67.202.148') ;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select user_url from wp_users;
+———————–+
| user_url |
+———————–+
| http://114.67.202.148 |
+———————–+
1 row in set (0.00 sec)

You May Also Like

MySQL mysqldump数据导出详解

TE 0

kubectl-in-action

TE 0

Network security for microservices with eBPF

TE 0

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注