Data Integration and Virtualisation
Category : Supporting Scripts
Create a MySQL or MariaDB view to show the size of each table in the database:
See database size for more information.
CREATE OR REPLACE VIEW table_size AS
SELECT table_schema
, table_name
, round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM information_schema.tables
WHERE table_schema IN ('dv', 'da', 'hue')
GROUP BY table_schema
, table_name
ORDER BY table_size_mb DESCCategory : Supporting Scripts
Create a MySQL or MariaDB view to show the aggregated size of each schema in the database:
See database size for more information.
CREATE OR REPLACE VIEW schema_size AS
SELECT table_schema
, round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM information_schema.tables
WHERE table_schema IN ('dv', 'da', 'hue')
GROUP BY table_schema
ORDER BY table_size_mb DESCCategory : How-to
Here is a super simple little tip for clearing the mysql.slow_log in MySQL or MariaDB when running an RDS on Amazon AWS.
For more information on AWS RDS, please see: https://aws.amazon.com/codedeploy/
Unfortunately the usual approach of simply DELETing or TRUNCATING data from the table doesn’t work due to a permission error. This is true, even for the AWS created master database user.
Error Code: 1044. Access denied for user 'masteruser'@'%' to database 'mysql'Luckily, the Amazon AWS team have put together a package that clears out the table for us.
CALL mysql.rds_rotate_slow_log;Category : How-to
Unlike recent versions of Maria DB, MySQL does not currently support the command EXECUTE IMMEDIATE.
Essentially EXECUTE IMMEDIATE is shorthand for perparing a statement, executing a statement and then finally deallocating the prepaired statement.
To get round this limitation in MySQL you can create a stored procedure that wraps up the commands required to execute a statement into a procedure so that you can call it as a one-liner.
CREATE PROCEDURE execute_immediate(IN query MEDIUMTEXT) MODIFIES SQL DATA SQL SECURITY DEFINER BEGIN SET @q = query; PREPARE stmt FROM @q; EXECUTE stmt; DEALLOCATE PREPARE stmt; END
You can then call the procedure as a one-liner like this:
CALL execute_immediate('QUERY GOES HERE');
Category : How-to
The MySQL and MariaDB server both use a file called my.cnf for parameters that are used to configure the server. This is where the port number and, if you use it, the local socket can be configured. The default port number for both MySQL and MariaDB is 3306 but you can change it as required.
A local socket is the prefered method of connecting to a database as it removes much of the overhead of creating a TCP connection and transferring data. This comes with the limitation that it can only be used if the application accessing the database is on the same machine. In larger or highly available systems this may not be possible.
A TCP connection is the only option of connecting to your MySQL or MariaDB database from a remote machine. It incurs a small penalty over a local socket and therefore slightly higher latencies. MySQL server and MariaDB can be configured to use a local socket, TCP connections or both.
We’ll be editing the my.cnf file for the following sections. Open the file in your favourite editor.
vi /etc/mysql/my.cnf
The socket option indicates the filesystem path to the location of the socket you’d like to use. Specify a filesystem path, usually /var/run/mysql/mysqld.sock and the socket will be created when the server next starts. Remove or comment (#) the line to disable socket access.
socket = /var/run/mysqld/mysqld.sock
Restart the server for the changes to take effect.
service mysql restart
The port option sets the MySQL or MariaDB server port number that will be used when listening for TCP/ IP connections. The default port number is 3306 but you can change it as required. Use the port option with the bind option to control the interface where the port will be listening. Use 0.0.0.0 to listen on all IP addresses on the host, or specify a single one directly to listen on a single interface. Omit both of these options to disable TCP/ IP connections.
port = 1234 bind = 10.10.10.10
Restart the server for the changes to take effect.
service mysql restart
Category : How-to
If you’ve lost or forgotten the root user password on a MySQL or MariaDB server you’ll want to reset it and leave all the other accounts and data intact. Fortunately it’s possible, but you’ll need access to an SSH account hosting the instance and the ability to stop and start the database service.
Before going any further, make sure your instance of MySQL or MariaDB is shutdown.
service mysql stop
Start the server in safe mode and don’t load the table grants and permissions.
mysqld_safe --skip-grant-tables &
Log into the local instance with the root user.
mysql -u root mysql
Run the below commands SQL, once connected, and reset your password. Be sure to substitute new-password with the new password for your root account.
use mysql;
UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';
FLUSH PRIVILEGES;
exit;
Finally, start the SQL server instance and use your new root account password.
service mysql restart mysql -u root -p