MySQL databases

By admin, February 20, 2018

Most of open source solutions make use of MySQL-based engines such as MariaDB. When well configured, they are indeed reliable and trustful.

Database administrators or support staff might need specific queries.  I came across situations that made me select some handful queries and patterns.

Query to verify size of the databases schemas in MySQL

SELECT table_schema AS ‘Database’,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS ‘Size (MB)’
FROM information_schema.TABLES
GROUP BY table_schema;

For importing big schemas
Prepare to import only the schema excluding all inserts of it (Fast)
Importing only the schema
cat database.sql | grep -v ^INSERT > schema_database.sql
Importing everything

MYSQL IMPORTS – typical error / problem

13:59:25 Restoring D:\work-hrs\client\20170517.220001.prod.database.sql
Running: mysql.exe –defaults-file=”c:\users\humberto\appdata\local\temp\tmpspj9fq.cnf” –protocol=tcp –host=localhost –user=root –port=7297 –default-character-set=utf8 –comments –database=sugarcrm-client < “D:\\work-hrs\\client\\20170517.220001.prod.database.sql”
ERROR 1153 (08S01) at line 5835: Got a packet bigger than ‘max_allowed_packet’ bytes

Operation failed with exitcode 1
14:06:30 Import of D:\work-hrs\client\20170517.220001.prod.database.sql has finished with 1 errors

Importing – exporting – Simple Solution

mysql -u username -p database_name < file.sql

or

mysql -h hostname -u username -p[password] database_name < file.sql

mysqldump db_name > backup-file.sql

or

mysql db_name < backup-file.sql

Saving disk space (ibdata, ibdata1…)

https://dba.stackexchange.com/questions/64134/deleting-reclaiming-space-from-innodb-table

Exporting databases

mysqldump -h stage-01.site.net -u useroot -p password > backupfile.sql

or

mysqldump   -u useroot -p  dbname > backupfile.sql

Masking Emails in MySQL

UPDATE  email_addresses SET email_address = CONCAT(LEFT(email_address, INSTR(email_address, ‘@’)), ‘example.com‘);
UPDATE  email_addresses SET email_address_caps = CONCAT(LEFT(email_address_caps, INSTR(email_address_caps, ‘@’)), ‘EXAMPLE.COM‘);
List of users
Might be useful to know better an environment and its restrictions
$mysql
mysql>select host, user, password from mysql.user;
OR
mysql>select host, user, authentication_string from mysql.user;
If you need to check the list of available fields, just do as you would do to ordinary tables, and select the fields you need to show.
mysql>desc mysql.user;
You should create a user for your CI-CD environment. You may have more than one if you use different IPs. You should use something apart from % for safety sake. However, here comes a general example.
mysql>CREATE USER ‘jenkins’@’ip’ IDENTIFIED BY ‘password’;
OR
mysql>CREATE USER ‘jenkins’@’%.subdomain.com’ IDENTIFIED BY ‘password’;
OR
mysql>CREATE USER ‘jenkins’@’192.168.2.%’ IDENTIFIED BY ‘password’;
If you made a mistake or the server has currently a unsafe policy, you could verify which users access the database, change permissions OR ask the application owners to chance the current user and  simply get rid of the users afterwards.
mysql>DROP USER ‘username’@‘localhost’;
mysql>GRANT ALL PRIVILEGES ON *.* TO ‘jenkins’@’%’ IDENTIFIED BY ‘password’;
Granting options may be important to a CI-CD tool work well. Thus:
mysql>GRANT ALL PRIVILEGES ON *.* TO ‘jenkins’@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;
mysql>FLUSH PRIVILEGES;
Now, if the binding var in your my.conf is correctly set, and the firewall allows it, you will be able to connect from the authorized hosts.
Issues connecting to a BD
Check this nice online tutorial for troubleshooting
https://www.tecmint.com/fix-error-2003-hy000-cant-connect-to-mysql-server-on-127-0-0-1-111/
Appart from that, if you are using iptables as firewall, you should consider checking if the port 3306 is open as input:
$iptables -L -n
And if not, try running
$iptables -I INPUT -p tcp –dport 3306 -j ACCEPT
More to come