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:54] smayr [Backup] |
systems:mysql_database_administration [2018/07/27 16:40] (current) smayr [Restore a database] |
||
---|---|---|---|
Line 67: | Line 67: | ||
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 '' | ||
Line 74: | Line 84: | ||
4. 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 83: | 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 121: | 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;</ |