References

Creating database, user and privileges

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

Setting user password

SET PASSWORD FOR 'dbname'@'localhost' = PASSWORD('password');
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('new-password-here') 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);
    }
}