# Export
mysqldump -u [username] -p[password] [database_name] > [dumpfilename.sql]
# Import
mysql -u [username] -p[password] [database_name] < [dumpfilename.sql]
Problem starting MySQL MariaDB: InnoDB: Set innodb_force_recovery to ignore this error.
After trying to import a database I had problem starting mysql.
# service mysql start
Job for mariadb.service failed because the control process exited with error code. See “systemctl status mariadb.service” and “journalctl -xe” for details.
# systemctl status mariadb.service
InnoDB: Set innodb_force_recovery to ignore this error.
To be able to delete tables in the database I had imported to I needed to use innodb_force_recovery
# nano /etc/mysql/my.cnf
I added the following line under [mysqld] in my.cnf
innodb_force_recovery=1
# service mysql start
I then logged into PHPmyAdmin and drop the tables in the database.
# nano /etc/mysql/my.cnf
I removed the line
innodb_force_recovery=1
# service mysql restart
Now everything worked. But I have to figure out why the db I tried to import messed up mysql….
Import a mysql database from terminal
mysql -u username -ppassword databasename < filename.sql
Example
Username: daniel
Password: 123Headadmin
Database name: my_new_db
Database file name: my_old_db.sql
mysql -u daniel -p123Headadmin my_new_db < my_old_db.sql
Rename a database in phpMyAdmin
I wanted to change a MySQL database name via phpMyAdmin. This is how I did it:
1. Login to phpMyAdmin.
2. Select the database you want to rename.
3. Click on operations tab.
4. In the “rename database to” section, specifiy the new name of the database.
5. You will get a question saying “CREATE DATABASE olddatabasename / DROP DATABASE newdatabasename”. Click ok.
6. Next you will be asked to reload the database. Click yes.
What happens is that you are creating a new database with a new name, dumping all tables of the old database into the new database and droping the old database. So remember that you will have to check your privileges becuase the old privileges from the old database will not be included.
If you are running a WordPress website you need to modify wp-config.php to start using the new database name.
MySQL backup script
1. Create a script folder.
# sudo mkdir /script
2. Create a backup folder and a destnation folder for the MySQL files.
# sudo mkdir /backup /backup/mysql
3. Create a script file.
# sudo nano /script/mysqlbackup.sh
At the start of the file add:
#!/bin/bash
On the next row write your script.
Here are a few script examples. In the examples the MySQL user is root and the password is Qwerty12.
Example 1. Will backup alla databases into one single file.
#!/bin/bash
/usr/bin/mysqldump –u root pQwerty12 –all-databases > /backup/mysql/all-databases.sql
Example 2. Will backup the database named mysite.
#!/bin/bash
/usr/bin/mysqldump –u root pQwerty12 –databases mysite > /backup/mysql/mysite.sql
When done exit Nano and save he script file.
4. Verify that the script file is correct.
# sudo cat /script/mysqlbackup.sh
5. Give the script execution rights.
# sudo chmod +x /script/mysqlbackup.sh
6. Instal Cron (Same as schedule tasks in Windows)
# sudo aptitude install cron
7. Edit crontab.
# crontab -e
Add the following t crontab:
01 23 * * * /script/mysqlbackup.sh
This means that mysqlbackup.sh will start every day 23.01.
8. Verify crontab.
# crontab -l
9. Verify the next day that the backup file exists in /backup/mysql/.
Run the mysqlbackup script manually:
# /script/./mysqlbackup.sh