Differences

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

--- systems:mysql_database_administration [2009/02/03 11:51]
smayr
+++ systems:mysql_database_administration [2018/07/27 16:40] (current)
smayr [Restore a database]
@@ Line 55: / Line 55: @@
 ==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
-Better yet, use [[#Backup_a_database|phpMyAdmin to backup]] the database.
-==Resetting Root Password==
+. 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:
+  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
+  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.
+. 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 68: / 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 75: / Line 117: @@
 </code>
 * Create the file ''config.inc.php''. You can use setup script provided in distribution (''scripts/setup.php'') to create basics of config file. See Setup chapter for details. If you don't like setup or want to fine tune resulting configuration, open (or create in case of starting from scratch) config.inc.php in your favorite editor and fill in there values for host, user, password and authentication mode to fit your environment. Edit the following fields if simply using a default file ''phpmyadmin/config.inc.php'': <code>
-$cfg['PmaAbsoluteUri']  = 'http://www..your-host.com/yourdirectory/';
+$cfg['PmaAbsoluteUri']  = 'http://www.your-host.com/yourdirectory/';
 $cfg['blowfish_secret'] = 'MySecretPhraseHere';
@@ Line 106: / 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>