References

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;

Backup database

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

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      |
+-------------+----------------+