Following are some quick tips related to Database Management:
While importing large database backups we may come across errors such as duplicate indexes. It would be useful to ignore such minor errors. The mysql command provides the —force switch which allows us to ignore the errors. For example:
sudo mysql -u {mysql_user} -p --force < {sql_file}To list all PostgreSQL databases, we need to first switch to user postgres using the command su psql. Next we need to login to the PostgreSQL console using the command PSQL. After that run the command \list. This will list all databases. To run queries on the database fist run the command \connect database_name. Then run your query.
If your Plesk settings are messed up then it may be useful to restore the backup of the Plesk databases. Plesk automatically backups up the following databases every day: psa, horde and mysql. For example the psa database can be restored with following command: plesk db psa < psa.sql
To login to MySQL server as root privileges on a Plesk server we can use the following command: MYSQL_PWD=cat /etc/psa/.psa.shadow mysql -u admin
To delete a Mongo Db database called test, enter the command: mongo. This will take you to the Mongo Db shell. Then enter the commands:
use test;
db.runCommand( { dropDatabase: 1 } );To backup a Mongo Db database, we can use the command: mongodump —archive=test.gz —gzip —db test. It will create a gzip backup of the test database
The MySQL tuner is a Perl script that checks a MySQL server and gives suggestions on how to improve its performance The script checks MySQL server log files and configuration and suggests optimal values for server configuration variables such as query_cache_size.
If you get following error on your MySQL/MariaDB server: General error: 23 Out of resources, then a possible solution is to increase the open_file_limit mysql variable. On Centos 7, this can be done by first creating the folder: /etc/systemd/system/mariadb.service.d/, then create a new file limits.conf with the following contents:
[Service]
LimitNOFILE=20000After that restart the MySQL/MariaDB service with the command:
systemctl daemon-reload
systemctl restart mariadb.serviceTo sort a MySQL string column containing a number we can use the SUBSTRING and CAST functions. The SUBSTRING function should extract the numbers inside the string. The CAST function will convert the extracted string to a number.
If you have problems with setting the user password on MariaDB or MySQL, for example you set the user password and are not able to login with the new password, then the problem may be caused because the database user is configured to use the wrong authentication plugin. For example unix_socket plugin instead of the default authentication scheme.
The solution is to set the plugin field in the user table of the MySQL database to empty value. This will cause the MySQL/MariaDB server to authenticate the database user with the default authentication scheme.
In MySQL 5.7.5 the ONLY_FULL_GROUP_BY SQL mode was introduced. In this mode, Non-deterministic grouping queries are rejected.
Applications that use GROUP BY clause in SQL queries may start giving errors after an upgrade to the MySQL server. To allow the applications to continue working, we need to remove the ONLY_FULL_GROUP_BY option from the SQL mode variable. This can be done by setting the SQL mode variable in /etc/mysql/mysql.conf.d/mysqld.cnf without the ONLY_FULL_GROUP_BY option. After that the MySQL server should be restarted. To get the current value of the SQL mode variable go to the variables tab in PhpMyAdmin and copy the value of SQL mode variable.
Sometimes we need to rename our MySQL database tables in bulk. For example we may decide to add or change the table prefix on all tables within a database. Database management tools such as PhpMyAdmin allow us to rename tables with a few clicks, but most tools do not allow batch renaming.
The article In MySQL, how do I batch rename tables within a database? suggests that to rename several MySQL tables at once, we can run a SQL query that fetches the list of all database tables from the information_schema.tables table. The SQL query can generate rename table statements. We can then export these rename table statements to a file. We can further process the table names using the find/replace feature of standard text editors. Next we can run the SQL queries for renaming the database tables.
For example the following SQL query will append the text “_old” to all tables in the “db” database. The query will not actually rename any tables. It will simply generate rename table statements. In order to restrict the renaming to certain tables, we can update the WHERE condition.
SELECT concat ('rename table ', table_name, ' to ', table_name, '_old;') FROM information_schema.tables WHERE table_schema='db'It is possible to calculate a running balance using only MySQL. A running balance is similar to the last column of a bank statement. Since the running balance depends on the balance of the previous row, we need to use a temporary table. The following three SQL queries can be used to calculate a running balance:
1. create TEMPORARY table tbl_temp (select * from tbl);
2. update tbl t1 set t1.Bal=(SELECT SUM(t2.deposited_amount-t2.issued_amount)
FROM tbl_temp t2 WHERE t2.id<=t1.id);
3. select * from tbl;To see which SQL queries are currently running on your MySQL server, enter the following SQL commands:
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';This will log the SQL queries to the table: “general_log” in MySQL database.
To see which slow SQL queries are currently running on your MySQL server, enter the following SQL commands:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL log_output = 'TABLE';This will log all the slow SQL queries to the table: “slow_log” in MySQL database. It will also log queries that are not using indexes.