Basic MySQL Commands
Related Posts
Security
Securing A Fresh MySql Installation
Answer all the questions in this security script that comes with MySQL installations.
sudo mysql_secure_installation
Show Accounts With No Password
Ideally there should be none of these!
SELECT User,Password,Host FROM mysql.user WHERE Password='';
Remove anonymous user
drop user ''@localhost
drop user ''
Change Root Password
Changing root password (change abc with your current password and 123456 with your desired new one).
mysqladmin -u root -p'abc' password '123456'
Setting Up Password For First Time
On Ubuntu 18.04, after installing mysql-server-5.7 you need to set the password by logging into the mysql CLI with
sudo mysql
... then set the password with:
GRANT ALL
ON *.*
TO root@localhost
IDENTIFIED BY 'PASSWORD_HERE';
GRANT GRANT OPTION
ON *.*
TO root@localhost
IDENTIFIED BY 'PASSWORD_HERE';
FLUSH PRIVILEGES;
If you are using CentOS and just perform a yum install mysql-server, you may notice that it never asks you for a password. By default there is no password and you can just log in as root. In fact if you specify password, then it will fail. To set the root user's password for the first time, run the following command.
mysqladmin -u root password [your password here]
User Administration
List Users
SELECT Host, User FROM mysql.user;
Add Fully Priviledged Local User
GRANT ALL
ON `DATABASE_NAME_HERE`.*
TO 'USERNAME_HERE'@'localhost'
IDENTIFIED BY 'PASSWORD_HERE';
FLUSH PRIVILEGES;
Adding a read-only local user to a database
GRANT SELECT \
ON `DATABASE_NAME_HERE`.* \
TO 'USERNAME_HERE'@'localhost' \
IDENTIFIED BY 'PASSWORD_HERE';
FLUSH PRIVILEGES;
Add a remote user to a MySQL
Adding a user to a MySQL and allow them to connect remotely from any IP
GRANT ALL ON *.* to [username]@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
This will also work
GRANT ALL ON *.* to [username] IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
GRANT ALL ON *.* which will give the user all privileges across all databases. You may want to change this.
Give Remote root User Full Privileges
GRANT ALL ON *.* TO root WITH grant option;
Allow Users To Create Their Own Databases
Find out here.
Get User's Privileges / Grants
This will show you the permissions of the currently logged in user.
SHOW GRANTS
If you want to see the permissions of another user:
SHOW GRANTS FOR username
Revoke / Remove User Privileges
The following command will revoke permissions from the myDatabase database for the user called user1;
REVOKE ALL PRIVILEGES ON myDatabase.* FROM user1;
Data Integrity
To check your database for any issues, run the following command:
mysqlcheck -u [user] -p --databases [database name]
You can also use --all-databases to check all the databases, and --auto-repair to automatically try to fix problems, not just find them. E.g.
mysqlcheck -u root -p --all-databases --auto-repair
For more options, refer to the mysqlcheck documentation.
Dumping Databases
Dump a Single Database
This is useful for copying/moving/backups
mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
$DATABASE_NAME > $DATABASE_NAME.sql
If space is tight, you can dump to a compressed file like so:
mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h $HOST \
$DATABASE_NAME | gzip -c > $OUTPUT_FILENAME
If you want to be able to read the file, rather than being for an export/import, then it is much more human-readable if you use the --skip-extended-insert option:
mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
--skip-extended-insert \
$DATABASE_NAME > $DATABASE_NAME.sql
Dump All Databases
mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
$DATABASE_NAME > $DATABASE_NAME.sql
Dump Structure Of a Database
Dumping just the structure of a database is exactly the same but with the -d switch:
mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
-d $DATABASE_NAME > $DATABASE_NAME.sql
Dump Data Of a Database
Dumping just the data (no structure) of a database:
mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
--skip-triggers \
--compact \
--no-create-info \
$DATABASE_NAME > $DATABASE_NAME.sql
Dump Large Table
mysqldump \
--host $HOST \
--user $USER \
--password="$PASSWORD" \
--single-transaction \
--quick \
$DATABASE_NAME \
$TABLE_NAME > $TABLE_NAME.sql
--quick option is what is important here when dealing with large tables.
Also, by using --single-transaction we can be safe (if using InnoDB rather than MYISAM) by using a single transaction, and mysqldump will
not feel the need to lock the table.
Dump Table To CSV
mysqldump \
-u [user] \
--password="[password]" \
-t \
-T[dump folder path] [database name] [table name] \
--fields-terminated-by=','
/var/lib/mysql works. Debian users do not have to worry about this.
/etc/mysql/my.cnf file: secure_file_priv=""
Import Database
Importing a database from a dumped database (previous command) can be done like so:
mysql \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
$DATABASE_NAME < $DATABASE_NAME.sql
Misc
Initialize the MySQL data directory
If you just changed the datadir in your my.cnf file, then you probably want to initialize the new directory.
sudo mysql_install_db
Reinstall MySql
You may want to do this because cant login/set root password
sudo service mysql stop
sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean
sudo deluser mysql
Diagnosing Connections/Processes
If you ever get a "too many connections" error message, make sure to log into your database and run the following command to see what connections are open:
SHOW FULL PROCESSLIST
SHOW PROCESSLIST will suffice
References
- Nixcraft - Import MySQL Dumpfile, SQL Datafile Into My Database
- Nixcraft - MySQL Change root Password
- Digital Ocean - How to Create a New User and Grant Permissions in MySQL
- Mysqldump Data Only
- How To Geek - Dump just the table structure to a file in MySQL
- Ask Ubuntu - MySQL keeps failing to install
- Nixcraft - MySQL Change root Password
- ServerFault - mysqldump to a tar.gz
First published: 16th August 2018