MySQL snippets for backup, restore, users, statistics, etc..

mysql.tricks.sh

# Import SQL file (those too big for phpMyAdmin
mysql -u username -p -h localhost database-name < data.sql

# Copy data from one table schema to another (useful when structure is similar between tables)
INSERT INTO table1 ( `username`, `email`, `password`, `firstname`, `lastname`, `created`) 
SELECT `username`, `email`, `password`, `firstname`, `lastname`, UNIX_TIMESTAMP() FROM table2

# Make sure you set the RIGHT host when you create a new user (127.0.0.1 vs localhost vs an IP vs "%")

# Create new User and give them access to database
GRANT ALL ON demo.* TO user1@localhost IDENTIFIED BY 'mypassword';

# Give full access to a user
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' WITH GRANT OPTION;

# OR give full access to a user for a certain database
GRANT ALL PRIVILEGES ON databasename.* TO 'user'@'localhost' WITH GRANT OPTION;

# Change user password
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-PASSWORD-HERE") where User='tom';

# Change user hosts that can connect to all
mysql> use mysql;
mysql> update user set host='%' where user='username';
mysql> flush privileges;