{"id":3709,"date":"2023-11-11T11:43:58","date_gmt":"2023-11-11T16:43:58","guid":{"rendered":"https:\/\/osric.com\/chris\/accidental-developer\/?p=3709"},"modified":"2023-11-11T11:43:58","modified_gmt":"2023-11-11T16:43:58","slug":"migrating-database-servers","status":"publish","type":"post","link":"https:\/\/osric.com\/chris\/accidental-developer\/2023\/11\/migrating-database-servers\/","title":{"rendered":"Migrating database servers"},"content":{"rendered":"

As I’m migrating websites and applications from one server to another, I’m also migrating databases from one server to another.<\/p>\n

Even though I’ve done this dozens, if not hundreds, of times, I always find myself looking up how to do this. I’m migrating from one MySQL (MariaDB) servers to another MySQL (MariaDB), so relatively straightforward but still some command syntax I don’t remember off the top of my head.<\/p>\n

First, export the old database to a file:<\/p>\n

DBHOST=old-db-host.osric.com\r\nDBUSER=dbusername\r\nDBNAME=dbname\r\nmysqldump --add-drop-table -h $DBHOST -u $DBUSER -p $DBNAME >$DBNAME.07-NOV-2023.bak.sql<\/code><\/pre>\n
The this mysqldump<\/code> command produces output that will re-create the necessary tables and insert the data.<\/p>\n
In this case I’m not compressing the output, but it would be trivial to pipe the output of mysqldump<\/code> to a compression utility such as xz<\/code>, bzip2<\/code>, or gzip<\/code>. For my data, which is entirely text-based, any of these utilities performs well, although xz<\/code> achieves the best compression:<\/p>\n
mysqldump --add-drop-table -h $DBHOST -u $DBUSER -p $DBNAME | xz -c >$DBNAME.07-NOV-2023.bak.sql.xz\r\nmysqldump --add-drop-table -h $DBHOST -u $DBUSER -p $DBNAME | bzip2 -c >$DBNAME.07-NOV-2023.bak.sql.bz2\r\nmysqldump --add-drop-table -h $DBHOST -u $DBUSER -p $DBNAME | gzip -c >$DBNAME.07-NOV-2023.bak.sql.gz<\/code><\/pre>\n
Next, create the new database and a database user account. This assumes there is a database server running:<\/p>\n
sudo mysql -u root\r\nCREATE DATABASE dbname;\r\nCREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'your-t0p-s3cr3t-pa55w0rd';\r\nGRANT ALL PRIVILEGES ON dbname.* TO 'dbuser'@'localhost';<\/code><\/pre>\n
Note that the CREATE USER<\/code> and GRANT PRIVILEGES<\/code> commands will result in a “0 rows affected” message, which is normal:<\/p>\n
Query OK, 0 rows affected (0.002 sec)<\/code><\/pre>\n
There are other ways to create the database, see 7.4.2 Reloading SQL-Format Backups<\/a> in the MySQL documentation.<\/p>\n
Next, import the database from the file. This example uses the root user because I did not grant the dbuser PROCESS privileges (which are not table-level privileges):<\/p>\n
sudo mysql --user=root --host=localhost dbname <dbname.07-NOV-2023.bak.sql<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
As I’m migrating websites and applications from one server to another, I’m also migrating databases from one server to another. Even though I’ve done this dozens, if not hundreds, of times, I always find myself looking up how to do this. I’m migrating from one MySQL (MariaDB) servers to another MySQL (MariaDB), so relatively straightforward … Continue reading Migrating database servers<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[381],"tags":[572,36],"class_list":["post-3709","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mariadb","tag-mysql"],"_links":{"self":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/3709","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/comments?post=3709"}],"version-history":[{"count":10,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/3709\/revisions"}],"predecessor-version":[{"id":3720,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/3709\/revisions\/3720"}],"wp:attachment":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/media?parent=3709"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/categories?post=3709"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/tags?post=3709"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}