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
/etc/init.d/mysql stop/usr/bin/mysqld_safe --skip-grant-tables &mysql --user=root mysqlmysql> update user set Password=PASSWORD('P@ssword1') WHERE User='root';mysql> flush privileges;mysql> exit/etc/init.d/mysql startGroup output of SHOW COLUMNS into comma-delimited list
select group_concat(column_name order by ordinal_position)from information_schema.columnswhere 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.columnswhere 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
$ 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.sqlBackup database
Taking table data dump through socket connection
$ mysqldump -u root -p --opt [database name] > [database name].sqlTaking table data dump with column name through socket connection
$ mysqldump -u root -p --opt --complete-insert [database name] > [database name].sqlTaking table data dump with column name through ip and port. Use this if any error thrown as below
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect$ mysqldump -h 127.0.0.1 -P 3306 -u root -p --opt --complete-insert dbname > dbname.sqlBackup database using crontab
Open the crontab file
sudo crontab -eAdd the mysqldump command to backup DBNAME every day at 1:30.
30 1 * * * /usr/bin/mysqldump -u 'USERNAME' -p'PASSWORD' --opt DBNAME > /home/USER/path/to/DBNAME_`date +\%Y\%m\%d_\%H\%M`.sqlOr add the mysqldump command to gzip backup all databases every day at 2:15.
15 2 * * * /usr/bin/mysqldump -u 'USERNAME' -p'PASSWORD' --all-databases | gzip > /home/USER/path/to/DBNAME_$(date +%F_%T).sql.gzImport database
mysql -u root -p newdatabase < /path/to/newdatabase.sqlBackup 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:
apt-get install automysqlbackupRun the command by typing:
automysqlbackupThe main configuration file for automysqlbackup is located at “/etc/default/automysqlbackup”.
nano /etc/default/automysqlbackupThe default location for backups is “/var/lib/automysqlbackup”. Search this directory to see the structure of the backups:
ls /var/lib/automysqlbackupls -R /var/lib/automysqlbackup/dailyRunning the MySQL Server container image with Docker
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_cidocker ps -adocker exec -it <container-id> "bash"mysql -uroot -pHow 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:
$ brew install graphvizGenerate an SVG file of your diagram:
$ dot -Tsvg your_database.dot > your_database.svgTo convert SVG file to PNG using ImageMagick
convert your_database.svg your_database.pngor with transparent background
mogrify -background none -format png *.svgTo convert SVG file to PNG using libsrvg
cat your_database.svg | rsvg-convert -o your_database.pngQuery 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` = 2AND 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` = 1ORDER BY FIND_IN_SET(`eq`.`question_id`, `ex`.`exam_questions`)Here is the sample mysql tables for above queries
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 |+---------+---------------+----------------+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;# tab seperatedmysql -h 127.0.0.1 -P 3306 -u username -p databasename < sql_requests.sql > out.tsv# comma seperatedmysql -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# comma seperated with -e/--execute optionsmysql -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.csvCSV to TSV
awk 'BEGIN { FS=","; OFS="\t" } {gsub(/"/, "");$1=$1; print}' input.csv > output.tsvCSV to INSERT command script
awk 'NR==1{x=$0;next} {gsub(/,/,"\47,\47");printf "INSERT INTO COUNTRYENTITY (%s) VALUES (\47%s\47)\n",x,$0}' out.csv