MySQL a simple howto

Jorge

2023.10.02

2 mins to read

Home / MySQL a simple howto

Management commands

https://mariadb.com/kb/en/account-management-sql-commands/

Manage Databases

new database

CREATE DATABASE newdatabasename;

show databases

https://mariadb.com/kb/en/show-databases/

SHOW {DATABASES | SCHEMAS}
    [LIKE 'pattern' | WHERE expr]

to list, show, see all the databases

SHOW DATABASES;

drop database

https://mariadb.com/kb/en/drop-database/

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

Manage users

create user

CREATE USER username@localhost IDENTIFIED BY 'StrongPassword';

List users

SELECT User From mysql.user;
SELECT host, user FROM mysql.user;
SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccount FROM mysql.user;

to show passwords

SELECT User, Host, authentication_string FROM mysql.user;

show with hosts

SELECT User, Host, authentication_string FROM mysql.user;

user rights

SELECT User, Db, Host from mysql.db;

drop user

https://mariadb.com/kb/en/drop-user/

DROP USER [IF EXISTS] user_name [, user_name] ...

To delete, remove or drop a user

DROP USER usernametodelete@localhost;

Grant Privleges

https://mariadb.com/kb/en/grant/

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' WITH GRANT OPTION;

Character sets and collations

https://mariadb.com/kb/en/setting-character-sets-and-collations/

on creating

CREATE DATABASE db_name_foo CHARACTER SET = 'utf8mb4' COLLATE = 'utf8mb4_general_ci';

modify an existing one

ALTER DATABASE db_name_foo CHARACTER SET = 'utf8mb4' COLLATE = 'utf8mb4_general_ci';

chect default character set

SHOW CREATE DATABASE db_name_foo;

for existing databases show characters and schemas

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

Backup, migrate, export, import

export database

mysqldump -u DBUSER -p DBNAME > date-name-mysql-bkp.sql

recover database

mysql -u DBUSER -p DBNAME < mysql-backup-to-recover.sql

Fragmented fields

check fragmented fields

from https://serverfault.com/a/265885 view the fragmented tables:

select  ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables  where  DATA_FREE > 0;

also with another view

select ENGINE,
  concat(TABLE_SCHEMA, '.', TABLE_NAME) as table_name,
  round(DATA_LENGTH/1024/1024, 2) as data_length,
  round(INDEX_LENGTH/1024/1024, 2) as index_length,
  round(DATA_FREE/1024/1024, 2) as data_free,
  (data_free/(index_length+data_length)) as frag_ratio
FROM information_schema.tables
WHERE DATA_FREE > 0
ORDER BY frag_ratio DESC;

Optimize tables

Using mysqlcheck

mysqlcheck --optimize -A

Using a MySQL query

SELECT concat("OPTIMIZE TABLE ", table_schema,".",table_name,";")
FROM tables
WHERE DATA_FREE > 0
INTO OUTFILE '/tmp/optimize.sql';
SOURCE '/tmp/optimize.sql';

if this does not recognize the tables use information_schema.tables

Wordpress Custom Fields

Very usefull for WordPress

clean up custom fields orphaned

mysql -u DBUSER -p DBNAME

SHOW DATABASES;
USE dbname;
SHOW TABLES;
SHOW COLUMNS FROM wp_postmeta;
SHOW FULL COLUMNS FROM wp_postmeta;

show contents

SELECT * FROM wp_postmeta WHERE meta_value;

SELECT * FROM wp_postmeta WHERE meta_key LIKE 'stringfoobar%';

delete the info not needed stringfoobar stringfoobar% starts with %stringfoobar ends with %stringfoobar% has in the middle

DELETE FROM wp_postmeta WHERE meta_key LIKE '%stringfoobar%';

if custom type change

replace ‘foo-old’ for ‘bar-new’ in TABLE COLUMNS

UPDATE wp_posts SET post_type = 'bar-new' WHERE post_type = 'foo-old';

replace ‘foo_bar’ for ‘bar-foo’ in TABLE COLUMNS

UPDATE wp_posts SET post_type = replace(post_type,'foo_bar','bar-foo');
UPDATE wp_posts SET guid = replace(guid,'foo_bar','bar-foo');