Skip to content

MySQL

MySQL cheat sheet: upgrading to 8.x, users and privileges, password resets and data migration.

7 min read

References

Upgrading to MySQL from 5.x to 8.x

Dropping database, user

DROP DATABASE IF EXISTS dbname;
DROP USER dbname@localhost;

Creating database, user and privileges

CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER dbname@localhost IDENTIFIED BY 'P@ssword1';
GRANT ALL PRIVILEGES ON dbname.* TO dbname@localhost;
FLUSH PRIVILEGES;

Setting user password

SET PASSWORD FOR 'dbname'@'localhost' = PASSWORD('P@ssword1');
FLUSH PRIVILEGES;

Resetting a forgotten root password

Terminal window
/etc/init.d/mysql stop
/usr/bin/mysqld_safe --skip-grant-tables &
mysql --user=root mysql
mysql> update user set Password=PASSWORD('P@ssword1') WHERE User='root';
mysql> flush privileges;
mysql> exit
Terminal window
/etc/init.d/mysql start

Group output of SHOW COLUMNS into comma-delimited list

select group_concat(column_name order by ordinal_position)
from information_schema.columns
where table_schema = 'database_name' and table_name = 'table_name';

output

COLUMN1,COLUMN2,COLUMN3,COLUMN4,etc...

with apostrophe

select group_concat("`",column_name,"`" order by ordinal_position)
from information_schema.columns
where table_schema = 'database_name' and table_name = 'table_name';

output

`COLUMN1`,`COLUMN2`,`COLUMN3`,`COLUMN4`,etc...

random integer range

update `store_products_categories` set `category_id` = FLOOR( 2 + RAND( ) * 8 ) where category_id = 1;

Migrating data from one database to another database

insert into `dbDestination`.`tblDestination` (`field1`, `field2`, `field3`)
select `field1`, `field2`, `field3`
from `dbSource`.`tblSource`

Comparing structures of two databases

Terminal window
$ mysqldump --skip-comments --skip-extended-insert -d --no-data -u root -p dbName1 > file1.sql
$ mysqldump --skip-comments --skip-extended-insert -d --no-data -u root -p dbName2 > file2.sql
$ diff file1.sql file2.sql

Backup database

Taking table data dump through socket connection

Terminal window
$ mysqldump -u root -p --opt [database name] > [database name].sql

Taking table data dump with column name through socket connection

Terminal window
$ mysqldump -u root -p --opt --complete-insert [database name] > [database name].sql

Taking table data dump with column name through ip and port. Use this if any error thrown as below

Terminal window
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect
Terminal window
$ mysqldump -h 127.0.0.1 -P 3306 -u root -p --opt --complete-insert dbname > dbname.sql

Backup database using crontab

Open the crontab file

Terminal window
sudo crontab -e

Add the mysqldump command to backup DBNAME every day at 1:30.

Terminal window
30 1 * * * /usr/bin/mysqldump -u 'USERNAME' -p'PASSWORD' --opt DBNAME > /home/USER/path/to/DBNAME_`date +\%Y\%m\%d_\%H\%M`.sql

Or add the mysqldump command to gzip backup all databases every day at 2:15.

Terminal window
15 2 * * * /usr/bin/mysqldump -u 'USERNAME' -p'PASSWORD' --all-databases | gzip > /home/USER/path/to/DBNAME_$(date +%F_%T).sql.gz

Import database

Terminal window
mysql -u root -p newdatabase < /path/to/newdatabase.sql

Backup a MySQL Table to a Text File

SELECT * INTO OUTFILE 'table_backup_file' FROM name_of_table;

How to Backup MySQL Information using automysqlbackup

To install this program, type the following into the terminal:

Terminal window
apt-get install automysqlbackup

Run the command by typing:

Terminal window
automysqlbackup

The main configuration file for automysqlbackup is located at “/etc/default/automysqlbackup”.

Terminal window
nano /etc/default/automysqlbackup

The default location for backups is “/var/lib/automysqlbackup”. Search this directory to see the structure of the backups:

Terminal window
ls /var/lib/automysqlbackup
ls -R /var/lib/automysqlbackup/daily

Running the MySQL Server container image with Docker

Terminal window
docker run --name mysqldb -e MYSQL_USER=mysql -e MYSQL_PASSWORD=mysql -e MYSQL_DATABASE=sample -e MYSQL_ROOT_PASSWORD=<YourPassword> -p 3306:3306 -d mysql:latest --character-set-server=utf8 --collation-server=utf8_general_ci
docker ps -a
docker exec -it <container-id> "bash"
mysql -uroot -p

How to access MySQL instance on Docker using mysql-connector-java

pom dependency

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.43</version>
</dependency>

java code

public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sample", "root", "<YourPassword>");
Statement sta = conn.createStatement();
String sql = "DROP TABLE IF EXISTS Registration;";
sta.executeUpdate(sql);
System.out.println("Dropped table in given database...");
sql = "CREATE TABLE Registration "
+ "(id INTEGER not NULL, "
+ " first VARCHAR(255), "
+ " last VARCHAR(255), "
+ " age INTEGER, "
+ " PRIMARY KEY ( id ))";
sta.executeUpdate(sql);
System.out.println("Created table in given database...");
sql = "INSERT INTO Registration VALUES (100, 'Zara', 'Ali', 18)";
sta.executeUpdate(sql);
sql = "INSERT INTO Registration VALUES (101, 'Mahnaz', 'Fatma', 25)";
sta.executeUpdate(sql);
sql = "INSERT INTO Registration VALUES (102, 'Zaid', 'Khan', 30)";
sta.executeUpdate(sql);
sql = "INSERT INTO Registration VALUES(103, 'Sumit', 'Mittal', 28)";
sta.executeUpdate(sql);
System.out.println("Inserted records into the table...");
sql = "SELECT * FROM Registration";
ResultSet rs = sta.executeQuery(sql);
while (rs.next()) {
long id = rs.getLong("id");
String first = rs.getString("first");
String last = rs.getString("last");
int age = rs.getInt("age");
System.out.println("id: " + id + ", first: " + first + " last: " + last + ", age: " + age);
}
}

Data Type Storage Requirements

Data Type Storage Required
TINYBLOB L + 1 bytes, where L < 2^8 (256 bytes)
BLOB L + 2 bytes, where L < 2^16 (65 kilobytes)
MEDIUMBLOB L + 3 bytes, where L < 2^24 (16 megabytes)
LONGBLOB L + 4 bytes, where L < 2^32 (4 gigabytes)

Generation of UML, EER, ERD Diagrams

In MySQLWorkbench you can export EER Diagram to PNG, SVG, PDF and PostScript formats by following Reverse Engineer wizard steps.

In Sequel Pro you can export Graphviz dot files. The result is a basic table-based ERD. You can use graphviz to export the diagram to svg.

Install Sequel Pro, run the app, connect to your MySQL server and open the database you’d like to diagram. Go to File > Export > Graphviz Dot file, and save the file somewhere convenient.

Install graphviz from homebrew via your terminal:

Terminal window
$ brew install graphviz

Generate an SVG file of your diagram:

Terminal window
$ dot -Tsvg your_database.dot > your_database.svg

To convert SVG file to PNG using ImageMagick

Terminal window
convert your_database.svg your_database.png

or with transparent background

Terminal window
mogrify -background none -format png *.svg

To convert SVG file to PNG using libsrvg

Terminal window
cat your_database.svg | rsvg-convert -o your_database.png

Query with comma delimited values

Since IN() function is not a good way to retrieve fields with comma delimited values unless it’s the exact string, we will use FIND_IN_SET() function to retrive comma delimited integer values which points question_id numbers.

/*** ***/
SELECT *
FROM
`educa_exam` AS `ex`,
`educa_question` AS `eq`
WHERE `ex`.`exam_id` = 2
AND FIND_IN_SET(`eq`.`question_id`, `ex`.`exam_questions`)
ORDER BY FIND_IN_SET(`eq`.`question_id`, `ex`.`exam_questions`)
/*** With LEFT JOIN***/
SELECT *
FROM
`educa_exam` AS `ex`
LEFT JOIN `educa_question` AS `eq` ON FIND_IN_SET(`eq`.`question_id`, `ex`.`exam_questions`)
WHERE `ex`.`exam_id` = 1
ORDER BY FIND_IN_SET(`eq`.`question_id`, `ex`.`exam_questions`)

Here is the sample mysql tables for above queries

Terminal window
mysql> select exam_id, exam_title, exam_questions from educa_exam;
+---------+---------------+----------------+
| exam_id | exam_title | exam_questions |
+---------+---------------+----------------+
| 1 | deneme exam 1 | 1,2,5 |
| 2 | deneme exam 2 | 1,4,8 |
+---------+---------------+----------------+
Terminal window
mysql> select question_id, question_title from educa_question;
+-------------+----------------+
| question_id | question_title |
+-------------+----------------+
| 1 | question1 |
| 2 | question2 |
| 3 | question3 |
| 4 | question4 |
| 5 | question5 |
| 6 | question6 |
| 7 | question7 |
| 8 | question8 |
+-------------+----------------+

Copying one single row to insert into the same table

CREATE TEMPORARY TABLE tmptable SELECT * FROM teacher WHERE id = 1;
UPDATE tmptable SET id = 2 WHERE id = 1;
INSERT INTO teacher SELECT * FROM tmptable WHERE id;

Copying one column to another table

SET SQL_SAFE_UPDATES = 0;
INSERT INTO AudienceEntity_TAGS (AudienceEntity_ID, TAGS) (SELECT ID, COUNTRY FROM AUDIENCEENTITY WHERE COUNTRY!='');
SELECT * FROM AudienceEntity_TAGS;

Random from list

UPDATE `TABLE` SET OPT=ELT(0.5 + RAND() * 2, true, false);
UPDATE `TABLE` SET VERDICT=ELT(0.5 + RAND() * 3, 'VALID', 'RISKY', 'INVALID');

MySQL ELT() returns the string at the index number specified in the list of arguments. The first argument indicates the index of the string to be retrieved from the list of arguments.

It returns NULL when the index number is less than 1 or index number is greater than the number of the string specified as arguments.

Data export

sql_requests.sql

SELECT * FROM COUNTRYENTITY;
SELECT * FROM CITYENTITY;
Terminal window
# tab seperated
mysql -h 127.0.0.1 -P 3306 -u username -p databasename < sql_requests.sql > out.tsv
# comma seperated
mysql -h 127.0.0.1 -P 3306 -u username -p databasename < sql_requests.sql | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > out.csv
Terminal window
# comma seperated with -e/--execute options
mysql -h 127.0.0.1 -P 3306 -u username -p databasename -e "SELECT * FROM COUNTRYENTITY" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > out.csv

CSV to TSV

Terminal window
awk 'BEGIN { FS=","; OFS="\t" } {gsub(/"/, "");$1=$1; print}' input.csv > output.tsv

CSV to INSERT command script

Terminal window
awk 'NR==1{x=$0;next} {gsub(/,/,"\47,\47");printf "INSERT INTO COUNTRYENTITY (%s) VALUES (\47%s\47)\n",x,$0}' out.csv

Related articles

1 min read

SQL

SQL joins explained — inner, left, right and full outer — with examples.

  • Databases
1 min read

Database Migration

Migrating a MySQL database to PostgreSQL with pgloader, step by step.

  • Databases
2 min read

JDBC

JDBC connection pools, driver and DataSource classes per database, and local versus XA transactions.

  • Java & Jakarta EE
  • Databases

Type to search the whole site.