MySQL

Apr 28th, 2015

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 mysql
mysql> update user set Password=PASSWORD('P@ssword1') WHERE User='root';
mysql> flush privileges;
mysql> exit
/etc/init.d/mysql start

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.sql

Backup database

Taking table data dump through socket connection

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

Taking table data dump with column name through socket connection

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

```sh
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 printedin > printedin.sql

Backup database using crontab

Open the crontab file

sudo crontab -e

Add 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`.sql

Or 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.gz 

Import database

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:

apt-get install automysqlbackup

Run the command by typing:

automysqlbackup

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

nano /etc/default/automysqlbackup

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

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

Running 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_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:

$ brew install graphviz

Generate an SVG file of your diagram:

$ dot -Tsvg your_database.dot > your_database.svg

To convert SVG file to PNG using ImageMagick

convert your_database.svg your_database.png

To convert SVG file to PNG using libsrvg

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

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.