homecomputerlab

Show MySQL uptime:
mysqladmin version

Login to MySQL
mysql -h [hostname] -u [username] -p

Show databases
show databases;

Create database
create database [name];

Delete database
drop database [name];

Use database
use [database];

Show tables
show tables;

Show columns
show columns from [table];

Find and replace string
update [table_name] set [column_name] = replace([column_name],'[string_to_find]','[string_to_replace]');

Show users
SELECT user, host, password FROM mysql.user;

Create new user accessible only on localhost
CREATE USER '[User1]'@'localhost' IDENTIFIED BY '[password]';

Create new user accessible from anywhere
CREATE USER '[User1]'@'%' IDENTIFIED BY '[password]';

Grant permissions to all databases for User1 from localhost only
GRANT ALL PRIVILEGES ON *.* TO 'User1'@'localhost' WITH GRANT OPTION;

Grant permissions to specified database for User1 from anywhere
GRANT ALL PRIVILEGES ON [db_name].* TO 'User1'@'%' WITH GRANT OPTION;

Create a new user and grant permissions to database at once
GRANT ALL PRIVILEGES ON [db_name].* To 'user'@'hostname' IDENTIFIED BY '[password]';

Show users permissions
SHOW GRANTS FOR [username]@localhost;

To reset a users password see my earlier post here:
https://www.homecomputerlab.com/reset-mysql-root-user-password

Show MySQL Database sizes:
SELECT table_schema "DB Name", sum( data_length + index_length ) / 1024 / 1024 "DB Size in MB"
FROM information_schema.TABLES GROUP BY table_schema;

To backup and restore a mysql database see one of my earlier posts here:
https://www.homecomputerlab.com/backing-up-mysql-databases

ENABLE REMOTE ACCESS TO MYSQL DB
Edit /etc/my.cnf and locate line that reads as follows
[mysqld]

Make sure line skip-networking is commented (or remove line) and add the following line:
bind-address=[YOUR.SERVER.IP.ADDRESS]

For example, if your MySQL server IP is 10.10.10.1 the config should look like as follows:
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 10.10.10.1
# skip-networking

Also make sure you have access from outside for that user:
GRANT USAGE ON *.* TO '[User1]'@'%' IDENTIFIED BY '[password]';