Once I faced a situation wherein I had no option to delete the database and was limited to use only the MySQL command line. I wasted a lot of time find and trying different ways to delete all tables in the database. So, I thought it will be worth sharing things that ultimately worked for me.
$ mysqldump -hHOSTNAME -uUSERNAME -pPASSWORD --add-drop-table --no-data DB_NAME | grep -e '^DROP \| FOREIGN_KEY_CHECKS' | mysql -hHOSTNAME -uUSERNAME -pPASSWORD DB_NAME;
We need to import database on local machine when setting up a live website locally or when we are transferring hosting from one service provider to another. Let’s see how we can import MySQL database dump from command line
Assuming that we have created the database say ‘mynewdb’ in which we have to import.
Run the following command
$ mysql -u username -ppassword mynewdb < dbbackupfile.sql
It is necessary to take database backups at regular intervals to prevent loss of data. In this article we will see how we can backup mysql database using command line.
‘mysqldump’ command can be used to create backup of MySQL database as follows:
$ mysqldump -u username -ppassword dbname > dbname.sql
The above command assumes that you are running the ‘mysqldump’ command on the same server on which MySQL is installed.
Are you trying to import a MySql dump file and getting the following error: “#1452 – Cannot add or update a child row: a foreign key constraint fails”? Its because of the foreign key validation checks.
You can run the following statement to get detailed error information:
SHOW ENGINE INNODB STATUS;
For connecting to the MySQL server you need to use MySQL client program. It needs to be provided parameters such as the hostname, username, password etc. to connect to the MySQL Server; if no parameters are specified the default values are assumed. Any of the connection parameters can be specified to override the default values.
Connecting to a local server specifying username and password
# mysql --user=username --password=mypassword databasename; # mysql -u username -pmypassword databasename;
It is important to have passwords for all MySQL user accounts for securing the database. If anyone knows the username of an account with no password then he can successfully connect to the database server.
To assign or change a password for an existing account
mysql> SET PASSWORD FOR 'newuser'@'localhost' = PASSWORD('mypass');
NOTE: Only users such as root that have update access to the mysql database can change the password for other users.
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:
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.