Basic MySQL Administration
Related Posts
Table of Contents
- User Administration
- Data Integrity
- Dump / Backup Database
- Import / Restore Database
- Miscellaneous
- References
User Administration
List Users
SELECT User, Host FROM mysql.user;
Show Users With No Password
If you just want to see the users with no passwords, then run the command below. Ideally there should be none of these!
SELECT User,Password,Host FROM mysql.user WHERE Password='';
Create User
Create Local User
CREATE USER 'USERNAME'@'localhost' IDENTIFIED BY 'PASSWORD_HERE';
IDENTIFIED BY
clause in it, which would create the user
account if it did not exist. However, this will not work in MySQL 8.
Create Remote User
Adding a user to a MySQL and allow them to connect remotely from any IP
CREATE USER 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD_HERE';
CREATE USER 'USERNAME' IDENTIFIED BY 'PASSWORD_HERE';
would do the same thing.
Remove User
drop user 'usernameHere'@localhost
drop user 'usernameHere'
Remove Anonymous User
drop user ''@localhost
drop user ''
Show Privileges
Show User's Privileges
If you want to see the permissions of a specfic user:
SHOW GRANTS FOR username
If you just wish to view your own privileges then you can run:
SHOW GRANTS
Grant User Privileges
Give User Full Access To Database
Use one of these, whichever is appropriate.
GRANT ALL ON DATABASE_NAME.* to USERNAME@localhost;
GRANT ALL ON DATABASE_NAME.* to USERNAME@%;
Grant All Privileges On Specific Database To Local User
GRANT ALL
ON `DATABASE_NAME_HERE`.*
TO 'USERNAME_HERE'@'localhost'
Grant Read-only Access On Database To Local User
GRANT SELECT \
ON `DATABASE_NAME_HERE`.* \
TO 'USERNAME_HERE'@'localhost' \
IDENTIFIED BY 'PASSWORD_HERE';
FLUSH PRIVILEGES;
Grant All Privileges On All Databases To Local User
GRANT ALL ON *.* TO 'USERNAME_HERE'@'localhost'
Give Remote root User Full Privileges
GRANT ALL ON *.* TO root WITH grant option;
Allow Users To Create Their Own Databases
Find out here.
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;
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'
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.
Dump / Backup Database
No Tablespaces
I have added the option --no-tablespaces
to all of the commands below. This resolves a breaking change MySQL added in a minor update.
Refer to this stack overflow post for more info.
Dump a Single Database
This is useful for copying/moving/backups
mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
--no-tablespaces \
--column-statistics=0 \
--single-transaction \
$DATABASE_NAME > $DATABASE_NAME.sql
--single-transaction
flag prevents you getting the following error message: Access denied for user 'username'@'%' to database 'databaseName'" when using LOCK TABLES
Dumping RDS Single Database
If dumping an RDS database, you probably want to add --set-gtid-purged=OFF
which would look like so:
mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
--no-tablespaces \
--single-transaction \
--column-statistics=0 \
--set-gtid-purged=OFF \
$DATABASE_NAME > $DATABASE_NAME.sql
Using Gzip Compression
If space is tight, you can dump to a compressed file like so:
mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h $HOST \
--no-tablespaces \
--column-statistics=0 \
--single-transaction \
$DATABASE_NAME | gzip -c > $OUTPUT_FILENAME
If you are connecting to a remote host and the bandwidth is limited, then use the --compress
flag like so:
mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h $HOST \
--compress \
--no-tablespaces \
--column-statistics=0 \
$DATABASE_NAME > $DATABASE_NAME.sql
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 \
--no-tablespaces \
--column-statistics=0 \
--skip-extended-insert \
$DATABASE_NAME > $DATABASE_NAME.sql
Parallel Dump With MySQL Pump
I recently had to backup/dump a database that was well over 200 GB in size. Doing this was going to be slow unless I could do this in a parallelized way that would make full use of my 1 gig connection. Luckily, I was able to do this with mysqlpump.
CORE_COUNT=4
mysqlpump \
--user=$USERNAME \
--password \
--default-parallelism=$CORE_COUNT \
--databases myDatabase \
--set-gtid-purged=OFF \
--host=xxxx.xxxx.rds.amazonaws.com \
> myDatabase.sql
Dump All Databases
You can dump all of the databases by using the --all-databases
optional parameter.
mysqldump \
-u $USERNAME \
-p \
-h $HOST \
--no-tablespaces \
--column-statistics=0 \
--all-databases \
> all-databases.sql
Dump Structure Of a Database
Dumping just the structure of a database can be achieved by adding the
--no-data
parameter.
mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
--no-tablespaces \
--no-data \
--column-statistics=0 \
$DATABASE_NAME > $DATABASE_NAME.sql
Dump Data Only
Dumping just the data (no structure) of a database can be achieved
by using the --no-create-info
parameter.
mysqldump \
-u $USERNAME \
-p$PASSWORD \
-h localhost \
--no-tablespaces \
--column-statistics=0 \
--skip-triggers \
--compact \
--no-create-info \
$DATABASE_NAME > $DATABASE_NAME.sql
Dump Large Table
mysqldump \
--host $HOST \
--user $USER \
--password="$PASSWORD" \
--no-tablespaces \
--column-statistics=0 \
--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]" \
--no-tablespaces \
--column-statistics=0 \
-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=""
Dump Grants
If you want to export the users/permissions/grants so that you can set up a replacement database, you can do this with the pt-show-grants tool.
Install the tool with:
sudo apt-get install percona-toolkit
Then you can create an SQL with all the commands for creating all the users/grants with:
pt-show-grants --host=$HOST --user=$USER --ask-pass > grants.sql
Import / Restore 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
Login Without Password (Scripts)
If you need the ability to be able to log in without passing a password, such as for a script, you could create a .my.cnf
file in your $HOME directory with the login credentials.
E.g.
touch ~/.my.cnf
chmod 0600 ~/.my.cnf
Then put put the following content in it:
[client]
user=myMysqlUser
password=myMysqlUsersPassword
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
Securing A Fresh MySql Installation
Answer all the questions in this security script that comes with MySQL installations.
sudo mysql_secure_installation
System Variables
Output System Variable
Sometimes you need to see what your system variables are set to. You can do this from the shell with:
SHOW VARIABLES LIKE '$VARIABLE_NAME';
For example, to output the current max_allowed_packet
setting, you would run:
SHOW VARIABLES LIKE 'max_allowed_packet';
MariaDB System Variables
MySQL System Variables
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
- Serverfault - mysqldump throws: Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
- Stack Overflow - How to auto login in MySQL from a shell script?
- Reddit - Using --single-transaction with mysqldump in RDS
- Stack Overflow - How to Export & Import Existing User (with its Privileges!)
First published: 28th August 2024