The DROP USER statement is used to delete MySQL accounts and their privileges.

DROP USER 'accountuser'@'localhost';

If you wish to delete multiple user accounts then you can enter them comma separated as shown below:

DROP USER 'accountuser1'@'localhost', DROP USER 'accountuser2'@'localhost';

MySQL user names can be up to 16 characters long.

Here are the queries for creating user accounts in MySQL:

Create user with no password

mysql> CREATE USER 'newuser'@'localhost';

Create user with password test123

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'test123';

To enable binary logging open the MySql configuration file my.cnf /my.ini and add the following line to the end of [mysqld] section and restart the server:

log_bin=base_name

Lets say base_name is given as ‘mysql-bin’.

This will write the binary log files in the MySql data directory. Mysql appends a numeric extension to the binary log basename to generate binary log file names. The number increases each time the server creates a new log file, thus creating an ordered series of files.

Optimizing Table in database is just like the doing Disk Defragmentation in your PC.

Optimize Table should be used if you have deleted a large part of a table or if you have made many changes to a table with variable length rows, such as VARCHAR, TEXT, BLOB or VARBINARY columns. Deleted rows are maintained in the linked list and insert operations reuse the old row positions. You can use “Optimize Table” to reclaim unused space and defragment the data file for optimal performance. If a lot of changes have been made to a table, optimizing the table can sometimes significantly improve performance.

My C:\ drive partion space was almost full, so I tried to analyse junk data on my machine to free some space. I noticed that MySql ‘data’ folder was using around 16GB. Further looking into the data folder I noticed that many mysql binary log files had been created and they were occupying GBs of the space.

After cleaning the log file the ‘data’ folder size reduced to ~6GB.

If you try importing a mysql database backup file greater than 2 MB then you may receive an error – “No data was received to import. Either no file name was submitted, or the file size exceeded the maximum size permitted by your PHP configuration. See FAQ 1.16.”. No Worries! you just need to make few changes to your php.ini file.

We can use the following command in Linux to duplicate database in mysql

mysqldump -hhost -uroot -p databasenameexport | mysql -hhost -uroot -p databasenameimport