MySQL / Mariadb cheatsheet
http://code.openark.org/blog/mysql/useful-database-analysis-queries-with-information_schema
Queries
WHERE Regex
Find where list of characters is contained:
-
WHERE myfield REGEXP '[A-Z -/]'
Find where list of characters is NOT contained:
-
WHERE myfield REGEXP '[^A-Z -/]'
Ubuntu / MySQL 5.7 connect as root from user account fix
- sudo mysql -u root
-
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypasswd';
-
Misc
Execute query on command line
mysql -e 'SHOW DATABASES'
Show table status
-
SHOW TABLE STATUS FROM db_name;
Change admin password
mysqladmin -u root -p password 'newpassword'
Change user password
SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass'); # Or for mysql 5.7: SET PASSWORD FOR 'bob'@'%.loc.gov' = 'newpass';
or more securely:
mysqladmin -u myuser -p -h mysql.example.com password
Fix Debian Sysmaintainer Password
- Get it from /etc/mysql/debian.cnf
-
SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('/etc/mysql/debian.cnf password'); # Or for mysql 5.7: SET PASSWORD FOR 'debian-sys-maint'@'localhost' = 'password from /etc/mysql/debian.cnf password';
Create table
-
CREATE TABLE stocks ( `id` int NOT NULL AUTO_INCREMENT, `slug` varchar(255), `date` date, `stueck_absolut` int(11), `kurs` decimal(15,2), `last_modified` datetime, `comment` varchar(255), PRIMARY KEY (id) )
Drop multiple default values at once
-
ALTER TABLE my_table ALTER COLUMN col1 DROP DEFAULT, ALTER COLUMN col2 DROP DEFAULT ;
Create user with MySQL 8
- CREATE USER foo@localhost IDENTIFIED WITH mysql_native_password BY 'secret';
- GRANT ALL ON foo.* TO foodb@localhost;
- FLUSH PRIVILEGES;
Reset mysql root password
Tested on Ubuntu 18.04
-
service mysql stop
-
mkdir -p /var/run/mysqld
-
chown mysql:mysql /var/run/mysqld
-
/usr/sbin/mysqld --skip-grant-tables --skip-networking &
-
mysql -u root
-
FLUSH PRIVILEGES;
-
USE mysql;
-
UPDATE user SET authentication_string=NULL WHERE User='root';
-
UPDATE user SET plugin="mysql_native_password" WHERE User='root';
-
quit
-
-
pkill mysqld
-
service mysql start
-
systemctl status mysql.service
mysqldump
mysqldump remote
- mysqldump --add-drop-table --no-tablespaces --host="mysql.example.com" --user="my_username" --password my_database > my_database.sql
mysqldump with limited privileges:
-
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
-
mysqldump --no-tablespaces
-
# mysqldump needs „PROCESS“ privilege if the --no-tablespaces option is not used
-
bz2 dump
mysqldump myDatabase --add-drop-table | bzip2 > myDump.sql.bz2
bzcat < myDump.sql.bz2 | mysql -D myDatabase
Show list of views
-
SHOW FULL TABLES IN usrdb_stunvmuu WHERE TABLE_TYPE LIKE 'VIEW';
Show sizes of databases
SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
Show table sizes
SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as size FROM information_schema.TABLES WHERE table_schema = "YOUR_DATABASE_NAME" ORDER BY size DESC;
Comparing dates without time
Get date without time:
SELECT DATE(NOW());
Use this to compare date colums, otherwise we would not get events for today:
SELECT * FROM EVENT WHERE date >= DATE(NOW());
Comparing between two databases with subquery
SELECT * FROM old_database.hardware o WHERE o.id NOT IN ( SELECT i.inventory_number from ullright.ull_ventory_item i) )
Correlated subquery, reference between query and subquery
Example within one database:
UPDATE ull_flow_doc d SET d.updated_at = ( SELECT max(m.created_at) FROM ull_flow_memory m WHERE d.id = m.ull_flow_doc_id ) ;
Between two databases:
UPDATE ullright.ull_entity e SET e.cost_center = ( SELECT u.kostenstelle FROM old_database.user u WHERE e.id = u.userid ) ;
Group a date field per month
SELECT COUNT(*), SUBSTR(d.created_at, 1, 7) AS month FROM ull_flow_doc d GROUP BY month;
GROUP_CONCAT directly create JSON which can be easily parsed to PHP array:
CONCAT('[', GROUP_CONCAT( DISTINCT CONCAT('{"id":"' , comment.id , '","name":"' , comment.name , '"}') ORDER BY comment.name SEPARATOR ',' ), ']') AS comments,
Search and Replace
UPDATE ull_cms_item_translation SET body=REPLACE(body, 'search', 'replace') WHERE body LIKE '%search%';
Find all columns LIKE
SELECT * FROM information_schema.columns WHERE column_name LIKE '%updated%;
Server Trouble Shooting
- vi /var/log/mysql/error.log
Startup fails, no log entries:
- sudo -u mysql mysqld
Message "mysqld: Error on realpath() on '/var/lib/mysql-files' (Error 2)
161026 10:44:40 [ERROR] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files":
- vi /etc/mysql/my.cnf
-
[mysqld] secure-file-priv = ""
-
Message "key_buffer is deprecated"
- vi /etc/mysql/my.cnf
- Rename "key_buffer" to "key_buffer_size"
Compare two databases
https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqldbcompare.html
- mysqldbcompare --run-all-tests --format=vertical --server1=root:pwd@localhost db1:db2 > diff.txt
Changing SQL Mode at Runtime
-
$res = mysql_query("SHOW VARIABLES LIKE 'sql_mode'");
var_dump(mysql_fetch_assoc($res));
mysql_query("SET SESSION sql_mode=''");
Copy MySQL data directory directly
Tested with Ubuntu 20.04
- service mysql stop
- Backup current mysql directory
- mv /var/lib/mysql /var/lib/mysql.bak
- Copy files from other installation
- cp -a /source/var/lib/mysql /var/lib/
- Fix user and group
- chown mysql:mysql /var/lib/mysql -R
- service mysql start
- Fix debian-sys-maint user
-
Get password from /etc/mysql/debian.cnf
-
cat /etc/mysql/debian.cnf
-
-
mysql -u root -p
-
SET PASSWORD FOR 'debian-sys-maint'@'localhost' = 'password from /etc/mysql/debian.cnf password';
-
-
Install MySQL Workbench
- Download apt repository package from https://dev.mysql.com/downloads/repo/apt/
- dpkg -i mysql-apt-config_0.8.16-1_all.deb
- Which product do you wish to configure:
- Server -> Select None
- Tools -> Select Enabled
- Previews: Select Disabled
- Which product do you wish to configure:
- apt update
- apt install mysql-workbench-community