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
MySQL Administration
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;
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
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;
3.5 CHARSET AND COLLATION
3.5.1 Query DB and Tables Collation
SELECT SCHEMA_NAME , DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA ;
+--------------------+------------------------+
| SCHEMA_NAME | DEFAULT_COLLATION_NAME |
+--------------------+------------------------+
| db_name | utf8mb4_uca1400_ai_ci |
+--------------------+------------------------+
select table_name,table_collation FROM information_schema.tables WHERE table_schema='db_name';
3.5.2 Modify DB & Tables Collation
ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
SELECT CONCAT(
'ALTER TABLE `', table_name,
'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
)
FROM information_schema.tables
WHERE table_schema='db_name';
