This guide will go through some mysql commands, these will also be relevant to Mariadb
Quick Links:
Deleting a user
Changing the host of a user
Changing a users password
Granting user privileges
mysql dump
mysql secure copy (scp)
Securing mysql
Remotely accessing your db
Finding out the size of a db or table
The Basics:
Enter mysql: mysql -u root -p
Show databases: show databases;
Select a database: use databasename;
Showing tables in the database: show tables;
Show users and where it can access the db from: select user, host from mysql.user;
Create a new mysql user: CREATE USER ‘luke’@’x.x.x.x’ IDENTIFIED BY ‘testpassword’;
If you have created a user without setting a password then you can set the password with: SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘password’);
Deleting a user: drop user ‘luke’@’x.x.x.x’;
Changing the host of a user (most commonly used to change from localhost to an IP address so the db can be accessed remotely).
UPDATE mysql.user SET host = ‘new ip/localhost’ WHERE user = ‘luke’
If you wish the user to be accessible from any location then you are able to add a wildcard ip address of ‘%‘. Please note that this is not secure and not recommended.
UPDATE mysql.user SET Password=PASSWORD(‘newpassword’) WHERE User=’user’ AND Host=’x.x.x.x’;
Granting User Privileges;
Granting all privileges to a user on a specific database: GRANT ALL PRIVILEGES ON databasename.* TO ‘user’@’x.x.x.x’;
Showing the privileges for a specific user: SHOW GRANTS FOR ‘user’@’x.x.x.x’;
After updating privileges you will need to flush them with:
flush privileges;
Note: x.x.x.x will be replaced with ip address. This could also be ‘localhost’ rather than an IP
Privileges available to grant to users:
Database manipulation SELECT........read only INSERT........insert rows/data UPDATE........change inserted rows/data DELETE........delete drop rows of data
Table manipulation: CREATE, DROP, ALTER
Mysql dump (exporting and importing databases)
To take a mysql dump you should not be logged into mysql, you will just need to be in the terminal. Use the following command to export the db to the directory you are currently in:
mysqldump -u root -p database > filename.sql
Once you have taken the dump you can then import the db using the following commands.
If you haven’t already prepared a blank database then you will need this command first:
mysqladmin -u root -p create databasename
Once you have created the db you can import with:
mysql -u root -p mytestdb2 < mytestdb.sql
Secure transfer of database to another server (scp)
Once you have taken a mysql dump you may want to transfer this to another server. You can do this using scp (secure copy).
scp [database name].sql [username]@[servername]:path/to/database/
Example:
scp dbdump.sql [email protected]:~/
The example above securely copies the db to the server x.x.x.x and places the file in the root directory. (note: you will need to enter the password for the destination user/server)
scp custom port (example 666):
scp -P 666 filename.sql [email protected]:/
Securing mysql
The best practice is to lock down and secure mysql as much as possible. You can use the following command to assist you with this:
sudo mysql_secure_installation
Remotely Accessing your database. Please make sure that the user has been created with the ability to be accessed remotely (e.g NOT from local host)
Iptables rule will also need to be added to the database server with the following command:
iptables -I INPUT 1 -p tcp –dport 3306 -j ACCEPT
To access a mysql server remotely you should use:
Finding out the size of a specific database:
SELECT table_schema “Data Base Name”,
sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”,
sum( data_free )/ 1024 / 1024 “Free Space in MB”
FROM information_schema.TABLES
WHERE table_schema = “$DATABASE_NAME”
Finding out the size of all databases:
(Note: you do not need to change the ‘Database Name’)
SELECT table_schema “Database Name”,
sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”,
sum( data_free )/ 1024 / 1024 “Free Space in MB”
FROM information_schema.TABLES
GROUP BY table_schema ;
Finding out the size of a table:
SELECT table_name AS “Table”,
round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB”
FROM information_schema.TABLES
WHERE table_schema = “$DATABASE_NAME”
AND table_name = “$TABLE_NAME”;