References
Upgrading to MySQL from 5.x to 8.x
Dropping database, user
1
2
|
DROP DATABASE IF EXISTS dbname;
DROP USER dbname@localhost;
|
Creating database, user and privileges
1
2
3
4
|
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
1
2
|
SET PASSWORD FOR 'dbname'@'localhost' = PASSWORD('P@ssword1');
FLUSH PRIVILEGES;
|
Resetting a forgotten root password
1
2
3
|
/etc/init.d/mysql stop
/usr/bin/mysqld_safe --skip-grant-tables &
mysql --user=root mysql
|
1
2
3
|
mysql> update user set Password=PASSWORD('P@ssword1') WHERE User='root';
mysql> flush privileges;
mysql> exit
|
1
|
/etc/init.d/mysql start
|
Group output of SHOW COLUMNS into comma-delimited list
1
2
3
|
select group_concat(column_name order by ordinal_position)
from information_schema.columns
where table_schema = 'database_name' and table_name = 'table_name';
|
output
1
|
COLUMN1,COLUMN2,COLUMN3,COLUMN4,etc...
|
with apostrophe
1
2
3
|
select group_concat("`",column_name,"`" order by ordinal_position)
from information_schema.columns
where table_schema = 'database_name' and table_name = 'table_name';
|
output
1
|
`COLUMN1`,`COLUMN2`,`COLUMN3`,`COLUMN4`,etc...
|
random integer range
1
|
update `store_products_categories` set `category_id` = FLOOR( 2 + RAND( ) * 8 ) where category_id = 1;
|
Migrating data from one database to another database
1
2
3
|
insert into `dbDestination`.`tblDestination` (`field1`, `field2`, `field3`)
select `field1`, `field2`, `field3`
from `dbSource`.`tblSource`
|
Comparing structures of two databases
1
2
3
|
$ 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
1
|
$ mysqldump -u root -p --opt [database name] > [database name].sql
|
Taking table data dump with column name through socket connection
1
|
$ 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
1
|
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect
|
1
|
$ 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
Add the mysqldump command to backup DBNAME every day at 1:30.
1
|
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.
1
|
15 2 * * * /usr/bin/mysqldump -u 'USERNAME' -p'PASSWORD' --all-databases | gzip > /home/USER/path/to/DBNAME_$(date +%F_%T).sql.gz
|
Import database
1
|
mysql -u root -p newdatabase < /path/to/newdatabase.sql
|
Backup a MySQL Table to a Text File
1
|
SELECT * INTO OUTFILE 'table_backup_file' FROM name_of_table;
|
To install this program, type the following into the terminal:
1
|
apt-get install automysqlbackup
|
Run the command by typing:
The main configuration file for automysqlbackup is located at “/etc/default/automysqlbackup”.
1
|
nano /etc/default/automysqlbackup
|
The default location for backups is “/var/lib/automysqlbackup”. Search this directory to see the structure of the backups:
1
2
|
ls /var/lib/automysqlbackup
ls -R /var/lib/automysqlbackup/daily
|
Running the MySQL Server container image with Docker
1
2
3
4
|
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
1
2
3
4
5
|
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.43</version>
</dependency>
|
java code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
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:
1
|
$ brew install graphviz
|
Generate an SVG file of your diagram:
1
|
$ dot -Tsvg your_database.dot > your_database.svg
|
To convert SVG file to PNG using ImageMagick
1
|
convert your_database.svg your_database.png
|
or with transparent background
1
|
mogrify -background none -format png *.svg
|
To convert SVG file to PNG using libsrvg
1
|
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
/*** ***/
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
1
2
3
4
5
6
7
|
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 |
+---------+---------------+----------------+
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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
1
2
3
|
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
1
2
3
|
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
1
2
|
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
1
2
|
SELECT * FROM COUNTRYENTITY;
SELECT * FROM CITYENTITY;
|
1
2
3
4
|
# 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
|
1
2
|
# 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
1
|
awk 'BEGIN { FS=","; OFS="\t" } {gsub(/"/, "");$1=$1; print}' input.csv > output.tsv
|
CSV to INSERT command script
1
|
awk 'NR==1{x=$0;next} {gsub(/,/,"\47,\47");printf "INSERT INTO COUNTRYENTITY (%s) VALUES (\47%s\47)\n",x,$0}' out.csv
|