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
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
Related
