infostreams / db, Hacker News

With DB you can very easily save, restore, and archive snapshots of your database from the command line. It Supports connecting to different database servers (for example a local development server and a staging or production server) and allows you to load a database dump from one environment into another environment.

(****************************************

[new collation] $ db save localhost

snapshot before running migrations“hashf4f0c1d3fac  (c) ************************************************************************************************************************************************************************** (a) ********************************************************************************************************************************** (cdaa5d) ************************************************************************************************************************************************ (dcd4b) ****************************************************************************************************************************************** (c6e) ******************************************************************************************************************************** (ccb) **********************************************************************************************************************

By default, this repository lives in the db

folder in your project root. In this repository are gzipped database dumps. Whether or not you want to commit the contents of that folder to git

or another VCS is up to you. If you do, make sure to exclude your database credentials by adding something like .db / * / config / credentials.cnf

(untested!) to your
. gitignore (************************************. 
Imagine the following scenario: After creating the local snapshot, you run a database migration. However, the migration breaks your database and deletes some stuff that you didn't want to delete. Usually, if you run a migration, there is also a way to undo that migration - but alas, that won't return your deleted data. No problem. To restore your database to the state it was in before you started the migration, simply run:
[new collation] $ db load localhost f4f0c1d3fac (c) *************************************************************************************************************************************************************************************** (a) ********************************************************************************************************************** (cdaa5d) ********************************************************************************************************************************************** (dcd4b) ***************************************************************************************************************************************** (c6e) ********************************************************************************************************************************** (ccb) ************************************************************************************************************************* ********************************
This will load the snapshot you made before you ran the migration. It's like the migration never happened.

There are many other things (db) can do, for example pulling down a database from the staging environment to your localhost:

[new collation] $ db save staging
Snapshot intended for development* hash******************************************************************************************************************************* (b) *********************************************************************************************************************************** (c) ************************************************************************************************************************************************ (eb) ******************************************************************************************************************************************** (d5b) ************************************************************************************************************************************************************************ (e) ***************************************************************************************************************************************** (f) ********************************************************************************************************************** (f2bfca) ************************************************************************************************************************************************************************** (b5b) **************************************************************************************************************************************** (e) ********************************************************************************************************************  $ db load localhost  (b) *********************************************************************************************************************************** (c) ************************************************************************************************************************************************ (eb) ********************************************************************************************************************************************* (d5b) **************************************************************************************************************************************************************** (e) *************************************************************************************************************************************** (f) ************************************************************************************************************************** (f2bfca) **************************************************************************************************************************************************************************** (b5b) ****************************************************************************************************************************** e 79214713088Here you first make a snapshot of the database on the  (staging) ************************************** server, which you then load into your  (localhost)  database. This works most reliably if your staging and your development server are on the same database version.See the full  list of available commandsget a complete overview ofdb's capabilities.******************************** [new collation] ************************ [serveralias|all] ******************************  InstallationFor now, you can only install  (db)  by cloning this repository:[new collation] $ git clone https://www.github.com/infostreams/db (********************************and then creating a symlink to the main  (db)  script from a directory that is in your path, eg:[new collation] $cd  (db /) #change to the directory you cloned the github repository in$ ln -s db / usr / local / bin / (#)  create the symlink*******************************
You can see if it works by running

If all is well you should see the following friendly error message:

[new collation] fatal: Not a db repository (or any of the parent directories). Please run'db init' (********************
So, to really get started, go to a directory where you have a project that uses a database, and type(db init
. This will start the process of setting up your database connection details, after which the following commands will be available to you.
********************************** [serveralias|all] ************************** Available commands



dbhas commands to import external SQL files into your database (

db import
), to export a dump from the repository to a file (db export ), to make minor changes to your database (such as changing the encoding, withdb change ), to clean out your database entirely (

db nuke
), or to show the table structure of a particular dump (db structure  [serveralias|all] ). Very high on the wish list is a way to display a "diff" between two dumps, but that's not so easy and hasn't been implemented yet.


Full list

The full list of available commands is as follows
init  [new collation] **************************************
(save) **********************************************
(load) ************************************************************************
(remove************************************************
(server)
export[new collation] ************************************
(import

(log
nuke**************************************************
(show) ************************
structure****************************************************
change[new collation] **************************************
(test) ************** (****************************************************** (db init) ************************************************
Creates a new DB repository in the current directory. Starts an interactive session that allows you to specify the connection details.
(**************************************************
****************** [serveralias|all] ************************** (Syntax)
[new collation] $ db init>******************************
If no database-type is provided, you will be asked to specify it in the interactive session. At the moment, only mysql is supported. The extension points for other database systems are already in place and could (theoretically) be added relatively easily.
Below you see an example of the questions you might be asked.

[new collation] $ db init  type*  forthis repositorytype  [commit message]:forthe databasealias[]:
The password for your connection will be stored in plain text, in a file that only the current user has read-access to (file mode). Make sure to not commit this file (db /  / config / credentials.cnf
) to source control!

The text mentions connection type  (phpmyadmin) , but it doesn't actually work. Don't use it.

If you want to add a remote server, you need to have ssh access to it. It is best if you have setup passwordless access, otherwise it will ask you for your password every time you interact with the remote server.
(**************************************************************** [new collation] ****************************** db save
Saves a snapshot of the database to the repository.
(**************************************************** [--match MATCH] ************ [serveralias|all] ************************** [serveralias|all] **************************** Syntax ****************************************************************
[new collation] $ db save [server alias] [commit message]

If you omit the  (server alias)  it will default to using the oldest server alias, in my case almost always  (localhost) .

Example

[new collation] $ db save localhost
Hello database* hash(e)  a  (b) ****************************************************************************************************************************************************** (e7efd0ee) ************************************************************************************************************************************ (bff) ********************************************************************************************************************** (a5fab6be) *************************************************************************************************************************************************************************************** [mysql] ******************************************************************************************************************** (a3f) *****************************************************************************************************************************************************If you try to make a snapshot of a database that did not change, it will not save a new snapshot.(******************************************************************db loadLoads a snapshot from the repository into the database.CAVEAT: Any tables that are  (not)  in the snapshot you are restoring are left untouched.  It only replaces the tables that are in the snapshot.  If you want to completely empty your database first, have a look at  db nuke **************************.(********************************************************  (****************************** () ************  Syntax[new collation] $ db load [server alias] [snapshot] [--match MATCH] [serveralias|all] ************** ([new collation]   (table _  (2>****************************table_n*** (table ******************************If you omit the  (server alias)  it will default to using the oldest server alias, in my case almost always  (localhost) .For[snapshot] [serveralias|all]  You can either provide the full hash (ege  (a) ********************************************************************************************************************* (b) ****************************************************************************************************************************************************** (e7efd0ee) ************************************************************************************************************************************ (bff) ************************************************************************************************************************** a5fab6be*********************************************************************************************************************************************************************** (be)  a3f 0600or you can provide just enough characters to uniquely identify a given dump (eg e  (a) ******************************************************************************************************************** (b4) )You can choose to only load one or more specific tables from this snapshot. For example, the following command will only restore the  wp_users  table to localhost:[new collation] $ db load localhost e  (a) ****************************************************************************************************************** (b) ************************************************************************************************************************************************** (e7efd0ee) ************************************************************************************************************************************* (bff) ************************************************************************************************************************** (a5fab6be) **************************************************************************************************************************************************************************************** (be) ********************************************************************************************************************************* (a3f) **************************************************************************************************************************************************** wp_usersYou can provide more than one table to restore. If you don't provide any tables, it will load all the tables that are defined in the snapshot.You can also provide a regular expression to match the table name to restore. For example, to only restore tables whose name matches the regular expression  wp _  _________________ , you can run the following command:[new collation] $ db load localhost e  (a) ****************************************************************************************************************** (b) ************************************************************************************************************************************************** (e7efd0ee) ************************************************************************************************************************************* (bff) ************************************************************************************************************************** (a5fab6be) **************************************************************************************************************************************************************************************** (be) ********************************************************************************************************************************* (a3f) **************************************************************************************************************************************************** --match“**************************** (wp _) ************************************************************************************************************************************************************************************ (_. *)(************************************************Removes a snapshot from the repository(********************************************************  [new collation] ******************************************************** Syntax ****************************************************************[new collation] $ db remove [server alias] [snapshot](**************************************************************  (************************ [new collation] ********************  (**************************** Alternative syntax************************************************Instead of typing out  db remove, you can also use an abbreviated version,db rm (************************************:[new collation] $ db rm [server alias] [snapshot](********************************************************************** (db server) ************************************************  (************************************************************  [new collation]db server addAdds a new database server(******************************************************************************** [serveralias|all] ************************ [serveralias|all] ************************** (**********************************************************************************************************Example[new collation] $ db server add  forthe databasealias[]: production************************************************************************************************************************************. 0.0.1This would have added a new server with the alias  (production) , which you can then use to save and load snapshots.The text mentions connection type  (phpmyadmin) , but it doesn't actually work. Don't use it.If you want to add a remote server, you need to have ssh access to it. It is best if you have setup passwordless access, otherwise it will ask you for your password every time you interact with the remote server.db server removeRemoves a database server alias and all the snapshots. Does not do affect the actual database server itself.(********************************************************************************** [new collation] ************************ [serveralias|all] ******************************  (Syntax) ****************** $ db server remove [server alias]  [serveralias|all] ********************db server listShows a list of which servers are available(************************************************************************************ [serveralias|all] ************************** Syntax[new collation] $ db server list  (********************************************************************************** [--match MATCH] ************** [serveralias|all] **************************** [new collation] ********************** db exportCopies a database snapshot from the repository to a local file [new collation]************************  Syntax [commit message][new collation] $ dbexport  [--match MATCH] ****************************path to output .sql file>Example[new collation] $ dbexport  (project a) ********************************************************************************************************************************************** (f1d) ****************************************************************************************************************** e  (fef) **************************************************************************************************************** (d8b) ******************************************************************************************************************* (ea) **************************************************************************************************************** (bc3)  ~  / Desktop / latest-dump.sql (************************************************************************************  (****************************** () ************  db importTries to load an external database dump into the database. Will not empty out the database first (!) - if you want that, make sure to rundb nuke first.You can import gzip compressed database dumps, as long as the file extension is (*************************************. gz (**************************************.**********************  Syntax[new collation] $ db import [server alias] [serveralias|all]Example[new collation] $ db import localhost~  / / Desktop / some-dump-from-a-colleague-or-customer.sql(************************************************************************************  [new collation] ******************************************************** db nukeDeletes all tables in your database  (!!)************************ Syntax [new collation] This will delete all the tables in your database. If you omit theserver alias  it will default to using the oldest server alias, in my case almost alwayslocalhost  [commit message]Use with care (obviously).(********************************************************************************** [] ******************db logShow a list of snapshots in the repository. By default, it will list all snapshots for all servers.(****************************************************************************** [serveralias|all] ********************** [serveralias|all] ****************************  Syntax[new collation] $ db log [serveralias|all]ExampleThis will list all snapshots in the repository.This will only list the snapshots for thelocalhost  (database.)   (******************************************************************************************  (****************************************db showDisplays the contents of a snapshot. Basically echos the contents of the database dump to STDOUT.(******************************************************************************** [serveralias|all] ************************ [serveralias|all] **************************You don't need to provide the server alias here, since snapshots have unique names.
Example[new collation] $ db show e9d  e  (b0f) ********************************************************************************************************************************************************* (d) *************************************************************************************************************************************************** (d) ******************************************************************************************************************************************************************** (aac) **************************************************************************************************************************************************** b4f c  (d) ****************************************************************************************************************************************** eaf6e  (e1c) **********************************************************************************************************************************************************************************|  (less) **********************************************************Displays the contents of the given database dump, and pipes it through  (less)  so you can inspect it.(****************************************************************************************** () **********************db structureDisplays the table structure of a given snapshot: shows all the  CREATE TABLE  Statements in the snapshot. Useful for debugging or inspecting the database.(********************************************************************************** [serveralias|all] ************************ [new collation]Syntax[new collation] $ db structure [snapshot]You don't need to provide the server alias here, since snapshots have unique names.Example[new collation] $ db structure c6d  de [new collation] ******************************************************************************************************************************************************************** (a8ddd0ec) ****************************************************************************************************************************************************** a6d8f7cc  (f)  bcf  (c6ab)  |  less [new collation]Displays the table definitions in the provided snapshot, and pipes it through  (less)  for inspection.db change [serveralias|all] ****************************************Allows you to make changes to a database. Does not operate on a snapshot but on the actual database itself.(************************************************************************************  [new collation] **************************db change charsetChanges the character set and collation of the database and its tables.(********************************************************************************************************************** [serveralias|all] ************************** (Syntax)[new collation] $ db change charset [server alias] [serveralias|all] [new collation]Example[new collation] $ db change charset localhost utf8mb4 utf8mb4_unicode_ci(**************************************************************  [new collation] ******************db change wordpressRuns a naive search and replace on the database to make changes to allow WordPress to run on a different domain name. Only use this if you cannot use  (WP-CLI) ************************ (for some reason.)   (******************************************************************************************************  [--match MATCH] ************************  (Syntax)[new collation] $ db change wordpress [server alias] [new collation]  [new collation]Example[new collation] $ db change wordpress localhost ()  http://www.my-new-domain.com/ [serveralias|all] **************** [new collation]  (********************************************************************** db test [server