Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
systems:mysql_database_administration [2016/08/27 22:48] smayr [Backup] |
systems:mysql_database_administration [2018/07/27 16:40] (current) smayr [Restore a database] |
||
---|---|---|---|
Line 55: | Line 55: | ||
==Backup== | ==Backup== | ||
- | Copy all the files in / | + | Use any of these methods: |
+ | |||
+ | 1. Copy all the files in / | ||
server1% cp / | server1% cp / | ||
server2% cp -r / | server2% cp -r / | ||
Line 62: | Line 64: | ||
Restart mysql server | Restart mysql server | ||
- | Copy data using '' | + | 2. Copy data using '' |
server1% mysqldump > dbase.mysql | server1% mysqldump > dbase.mysql | ||
server2% mysqldump < dbase.mysql | server2% mysqldump < dbase.mysql | ||
+ | | ||
+ | For backup database | ||
+ | % mysqldump -u root -pPassword -P3309 --routines --no-data originaldb > " | ||
+ | | ||
+ | For restoration of database | ||
+ | % mysql -u root -pPassword -P3309 newdb < " | ||
+ | | ||
+ | Alternatively, | ||
+ | % mysqldump -u user -pMyPass -d originaldb | mysql -u user -pMyPass -Dnewdb | ||
+ | The new database must exist already. The -d flag in the '' | ||
- | Archive and compress the data (Linux): | + | 3. Archive and compress the data (Linux): |
% tar -czvf / | % tar -czvf / | ||
| | ||
- | Better yet, use [[# | + | 4. Better yet, use [[# |
- | + | ||
- | ==Resetting Root Password== | + | |
+ | == Resetting Root Password == | ||
+ | ==== Windows ==== | ||
The procedure under Windows ((MySQL Documentation, | The procedure under Windows ((MySQL Documentation, | ||
* Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager: Start Menu -> Control Panel -> Administrative Tools -> Services. Then find the MySQL service in the list, and stop it. If your server is not running as a service, you may need to use the Task Manager to force it to stop. | * Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager: Start Menu -> Control Panel -> Administrative Tools -> Services. Then find the MySQL service in the list, and stop it. If your server is not running as a service, you may need to use the Task Manager to force it to stop. | ||
Line 81: | Line 93: | ||
* You should be able to connect using the new password. | * You should be able to connect using the new password. | ||
+ | ==== Linux ==== | ||
+ | Run these commands: | ||
+ | <code bash> | ||
+ | $ service mysql stop | ||
+ | $ mysqld_safe --skip-grant-tables & | ||
+ | $ mysql -u root | ||
+ | |||
+ | mysql> use mysql; | ||
+ | mysql> update user set authentication_string=PASSWORD(" | ||
+ | mysql> flush privileges; | ||
+ | mysql> quit | ||
+ | |||
+ | $ service mysql stop | ||
+ | $ service mysql start | ||
+ | $ mysql -u root -p | ||
+ | </ | ||
+ | NOTE: Note: prior to MySQL 5.7 the column was called '' | ||
==Using phpMyAdmin to Manage a Database== | ==Using phpMyAdmin to Manage a Database== | ||
===Quick Installation=== | ===Quick Installation=== | ||
Line 119: | Line 148: | ||
* Select compression: | * Select compression: | ||
* Press <Go> button. This unpacks and runs the SQL statements stored in that gzipped file. | * Press <Go> button. This unpacks and runs the SQL statements stored in that gzipped file. | ||
+ | * Include the following statement in an SQL file that has constraints and fails to import: <code sql>SET SESSION FOREIGN_KEY_CHECKS=0;</ |