MySQL : Database Copy

 1] How to speed up Database copy on MySQL

MySQL 8.0 : using datafiles copy between MySQL instances.

It is possible to copy a whole MySQL instance from one server to another : the 2 MySQL have to be same version.

1.1 Stop MySQL on both instances

# systemctl stop mysqld

1.2 Run copy

Every files should be copied from one server to another.

Ex. # scp -pr /var/lib/mysql/* <target_server>:/var/lib/mysql

1.3 Start MySQL on both instances

# systemctl start mysqld

2] MySQL : How to create Users

2.1 Users with READ-WRITE on every Databases

CREATE USER 'SYSTEM'@'%' IDENTIFIED BY '<psw>';
GRANT ALL PRIVILEGES ON * . * TO 'SYSTEM'@'%';
FLUSH PRIVILEGES;

2.2 Users with READ-ONLY on every Databases

CREATE USER 'user_int'@'*' IDENTIFIED BY 'SPMint3344!5566' ;

GRANT USAGE  ON *.* TO 'user_int'@'*' ;

GRANT SELECT,SHOW VIEW  ON *.* TO 'user_int'@'*';

2.3 Users from one machine with READ-ONLY on every Databases

CREATE USER 'user_int'@'10.228.18.53' IDENTIFIED BY 'SPMint3344!5566' ;

GRANT USAGE  ON *.* TO 'user_int'@'10.228.18.53' ;

GRANT SELECT,SHOW VIEW  ON *.* TO 'user_int'@'10.228.18.53';

flush privileges ;

3] MySQL : Query Dictionnary

3.1 CHECK VOL & CHARSET AND COLLATION

mysql> SELECT table_schema AS "Database", ceil((SUM(data_length)+SUM(index_length)) / 1024 / 1024) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema order by table_schema ;

+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| app_bi            |     14767 |
| app_ods           |      7951 |
| app_dwh           |      8485 |

3.2 Check PROC/FUNCTIONS OWNERS

SELECT ROUTINE_SCHEMA,ROUTINE_NAME,DEFINER FROM INFORMATION_SCHEMA.ROUTINES   WHERE ROUTINE_SCHEMA like 'APP_%' ;

3.3 Reverse Engineering of Users in a file

tee rev_cre_us.sql.txt;

SELECT CONCAT

("SHOW CREATE USER '",USER,"'@'",HOST,"';")    AS show_sql

FROM mysql.user

WHERE USER NOT IN ('root') and user not like 'mysql%' ;

notee;

3.4 Reverse Engineering of Grants in a file

tee rev_cre_us_gr.sql.txt;

SELECT CONCAT

("SHOW GRANTS FOR '",USER,"'@'",HOST,"';")    AS show_sql

FROM mysql.user

WHERE USER NOT IN ('root') and user not like 'mysql%' ;

notee;