MySQL stores all its data in the following location /var/lib/mysql/<database> Simply make a copy of these files and you have a backup, when restoring make sure its owned by mysql.
Log in with a user which has the correct privileges mysql> CREATE DATABASE port22; Query OK, 1 row affected (0.00 sec) Change to the new database. mysql> use port22; Database changed Create a new table with an auto increment ID, password and email fields. mysql> CREATE TABLE user ( -> id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> pass VARCHAR(64), -> email VARCHAR(255) -> ); Query OK, 0 rows affected (0.
To view all users. SELECT user FROM mysql.user; To add a MySQL user which is able to connect only from the local machine, and have access only to a database called soup. # mysql -u root -p INSERT INTO mysql.user (Host,User,Password) VALUES('localhost','username',PASSWORD('password')); GRANT USAGE ON *.* to username@localhost IDENTIFIED BY 'password'; FLUSH PRIVILEGES; GRANT ALL PRIVILEGES ON soup.* TO username@localhost; FLUSH PRIVILEGES; If you don’t run the first “FLUSH PRIVILEGES;” statemtnt you might get the following error:
The following command will back up your MySQL database to a file. mysqldump --add-drop-table -u user -p database > outputfile.sql The following will restore your database. mysql -u root -p database < outputfile.sql Or for a complete server back up and compression: mysqldump -u root -p --all-databases | gzip > output.sql.gz