MySQL Snippets

Take a backup

# entire database
mysqldump -p -u username db_name > db_name.sql

# just schema
mysqldump -p -u username --no-data db_name > schema.sql

# just data
mysqldump -p -u username --no-create-info db_name > data.sql

# just one table
mysqldump -p -u username db_name table_name > db_name.tabel_name.sql

# Other useful switches
--host localhost
--ignore-table=db_name.table_name

Load from backup

mysql -p -u username db_name < db_name.sql

Reset root password

# Easiest to run as root
su - root

# Stop mysql
systemctl stop mysql

# Create a SQL file to reset
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';" > ~/reset_root_password.sql

# Execute it via --init-file, then kill it with Ctrl-C
mysqld --init-file=~/reset_root_password.sql

# Clean up SQL file
rm ~/reset_root_password.sql

# Start mysql
systemctl start mysql