Allowing remote access to MySQL

Edit on GitHub
Apr 27, 2018
Databases
3 minutes

Allow remote access

1GRANT PRIVILEGES ON *.*
2TO 'user'@'host'
3IDENTIFIED BY 'password'
4WITH GRANT OPTION;

*.* means you’re granting access to all tables of all databases. This could be databaseName.* meaning you’re granting access to all tables of databaseName. Or you could further fine grain which specific tables of what database you want to give access to, like databaseName.tableName
host could be a wildcard % allowing access from all hosts with that user/pass combination, or it could be an IP or an FQDN

Importing ALL Gzipped databases

1mysql -uroot -pecare2@ < alldatabases.sql

For this command the databases need to exist or the alldatabases.sql script needs to contain the CREATE DATABASE queries for the databases. In order to have the CREATE DATABASE queries, you gotta dump the databases with the --add-drop-database option, like so:

1mysqldump --user=${OLD_DB_USER} --password=${OLD_DB_PASS} --add-drop-database --all-databases | gzip -9 > ${DB_DUMP_FILENAME}.sql.gz

--add-drop-database

Add a DROP DATABASE statement before each CREATE DATABASE statement. This option is typically used in conjunction with the –all-databases or –databases option because no CREATE DATABASE statements are written unless one of those options is specified.

Alternatively, you have to provide the database names of all the databases you dumped in that one compressed file

1gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you didn’t dump the database with the --add-drop-database option and didn’t provide individual database names, you’ll get an error that looks like this when improting a file that contains more than one database

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databa' at line 1

Scripting

When scripting, use --user and --password instead of -u and -p to avoid errors

Update root password

This is when you already know the current root password and want to update it

1SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

Get all MySQL users

1SELECT User FROM mysql.user; -- Get only usernames
2SELECT user, host FROM mysql.user; -- Get usernames and the host they are allowed to connect from
3SELECT DISTINCT user FROM mysql.user; -- Get only usernames with no repetitions of different hosts
4SELECT * FROM mysql.user; -- Get the entire mysql.users table

Delete a user

DROP USER 'blah'

See Privileges and Grants

1SHOW GRANTS; -- See grants for the current user
2SHOW GRANTS FOR foo -- See grants for a user called foo

Notes