Differences

This shows you the differences between two versions of the page.

--- systems:mysql_database_administration [2010/07/12 14:10]
smayr
+++ systems:mysql_database_administration [2018/07/27 16:40] (current)
smayr [Restore a database]
@@ Line 53: / Line 53: @@
 ==Other SQL Operations==
 * [[http://en.wikipedia.org/wiki/Comparison_of_SQL_syntax | Comparison of SQL Syntax]]
 ==Backup==
-Copy all the files in /var/lib/mysql/* to some backup directory (Linux):
+Use any of these methods:
-  % cp /var/lib/mysql/* /backup/mysql
-Archive and compress the data (Linux):
+. Copy all the files in /var/lib/mysql/* to some backup directory (Linux).  Make sure to copy all ''ib*'' files (e.g. ''ibdata1'', and optionally ''ib_logfile0'' and ''ib_logfile1'') if using InnoDB tables:
-  % tar -czvf /data/backup/mysql-data-backup.tar.gz /var/lib/mysql
+  server1% cp /var/lib/mysql/* /data/backup/mysql
+  server2% cp -r /data/backup/mysql /var/lib
+  server2% chown -R mysql:mysql /var/lib/mysql
+  server2% mysql_upgrade -u root -p --force
+  Restart mysql server
+. Copy data using ''mysqldump'':
+  server1% mysqldump > dbase.mysql
+  server2% mysqldump < dbase.mysql
-Better yet, use [[#Backup_a_database|phpMyAdmin to backup]] the database.
+  For backup database
+  % mysqldump -u root -pPassword -P3309 --routines --no-data originaldb > "d:\dbwithnodata.sql"
+  For restoration of database
+  % mysql -u root -pPassword -P3309 newdb < "d:\dbwithnodata.sql"
+  Alternatively, duplicate database in same server:
+  % mysqldump -u user -pMyPass -d originaldb | mysql -u user -pMyPass -Dnewdb
+  The new database must exist already. The -d flag in the ''mysqldump'' command prevents copying of data.
-==Resetting Root Password==
+. Archive and compress the data (Linux):
+  % tar -czvf /data/backup/mysql-data-backup.tar.gz /var/lib/mysql
+. Better yet, use [[#Backup_a_database|phpMyAdmin to backup]] the database.
+== Resetting Root Password ==
+==== Windows ====
 The procedure under Windows ((MySQL Documentation, http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html)):
 * 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 71: / Line 93: @@
 * 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("YOUR-NEW-ROOT-PASSWORD") where User='root';
+mysql> flush privileges;
+mysql> quit
+$ service mysql stop
+$ service mysql start
+$ mysql -u root -p
+</code>
+NOTE: Note: prior to MySQL 5.7 the column was called ''password'' instead of ''authentication_string''.
 ==Using phpMyAdmin to Manage a Database==
 ===Quick Installation===
@@ Line 109: / Line 148: @@
 * Select compression: GZipped.
 * 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;</code>