(Guide) How to add database details to your question

Question

As we got quite some questions dealing with migrating other CMS or custom databases to WordPress, we need a guide on how to attach the minimum needed data. Those posts have some views, but there are simply too many different systems out there to have an expert for everything. So if you want to keep your question open, follow this guide closely and improve your question.

1. Connect/ssh into your server (on the command line)

2. Connect to your (mysql) database:

   # Scheme:
   mysql -u {username} -p{password}
   # Example: user: root password: root
   mysql -u root -proot
   

3. Inspect the databases (mysql> just is the prefix of the command prompt)

   mysql> show databases
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | wordpress          |
   | mysql              |
   | performance_schema |
   +--------------------+
   

4. Select your database

   mysql> use wordpress
   # or: your custom database
   mysql> use custom_database
   

5. Show existing tables

   mysql> show tables;
   +---------------------------+
   | Tables_in_custom_database |
   +---------------------------+
   | custom_table              |
   +---------------------------+
   1 row in set (0.00 sec)
   

6. Show table structure (this must be mapped to WordPress)

   mysql> describe custom_table;
   +----------+--------------+------+-----+---------+----------------+
   | Field    | Type         | Null | Key | Default | Extra          |
   +----------+--------------+------+-----+---------+----------------+
   | id       | int(11)      | NO   | PRI | NULL    | auto_increment |
   | name     | varchar(255) | YES  |     | NULL    |                |
   | code     | varchar(255) | NO   |     | NULL    |                |
   | city     | varchar(255) | YES  |     | NULL    |                |
   | country  | varchar(255) | YES  |     | NULL    |                |
   | timezone | varchar(255) | YES  |     | NULL    |                |
   | location | point        | YES  |     | NULL    |                |
   | updated  | longtext     | YES  |     | NULL    |                |
   +----------+--------------+------+-----+---------+----------------+
   8 rows in set (0.09 sec)
   

7. Show example contents of that table row(s)

   mysql> select * from custom_table limit 0,1;
   +-------+------+------+---------+------+---------------+----------+------------------------------------------------------------------------------------------------+
   | id    | code | city | country | name | timezone      | location | updated                                                                                        |
   +-------+------+------+---------+------+---------------+----------+------------------------------------------------------------------------------------------------+
   | 96073 | AUT  | Wien | Austria | Wien | Vienna/Europe | Vienna   | a:1:{i:0;O:12:"DateTimeZone":2{s:13:"timezone_type";i:3;s:8:"timezone";s:13:"Europe/Vienna";}} |
   +-------+------+------+---------+------+---------------+----------+------------------------------------------------------------------------------------------------+
   1 row in set (0.00 sec)
   

Then Go and add all that output to your question.

Table list, table description and an example row is the minimum needed to understand what you want to migrate and why. The mark up from the command line is easy to read and easy to copy/paste (Hint: Windows users need to highlight and hit Enter to copy stdout output).

Answers to this question should please show either alternate ways or improve upon what is shown in this question already.

Answer 1

To get the SQL command to recreate a table use SHOW CREATE TABLE as it would be more useful than just seeing the table structure with DESCRIBE. If the question is potentially complex then it's easy for the readers to quickly recreate the database just by a copy / paste of the output.

The result would have to be picked out of the mysql-cli command though:

CREATE TABLE `wp_users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) NOT NULL DEFAULT '',
  `user_pass` varchar(64) NOT NULL DEFAULT '',
  `user_nicename` varchar(50) NOT NULL DEFAULT '',
  `user_email` varchar(100) NOT NULL DEFAULT '',
  `user_url` varchar(100) NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) NOT NULL DEFAULT '',
  `spam` tinyint(2) NOT NULL DEFAULT '0',
  `deleted` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`)
) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=utf8

This also provides the table indexes.

Answer 2

I've put together some basic commands for interacting with MySQL on the command line. Everybody who wants to getting started with the MySQL CLI should definitely also check out the MySQL tutorial series (by phpacademy) on YouTube.

Setup

Using the mysql command in the terminal will probably work with the default setup on a linux/server environment; using it locally on OS X with MAMP will require an alias to the MySQL server provided by MAMP. In this case we can add the following line to our .bash_profile:

alias mysql=/Applications/MAMP/Library/bin/mysql

Commands

Login: mysql -u [username] -p; (will prompt for password)

Show all databases: show databases;

Access database: mysql -u [username] -p [database]; (will prompt for password)

Create new database: create database [database];

Select database: use [database];

Show all tables: show tables;

Show table structure: describe [table];

Create new table with columns: CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);

Adding a column: ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);

Adding a column with an unique, auto-incrementing ID: ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

Inserting a record: INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');

Selecting records: SELECT * FROM [table];

Selecting parts of records: SELECT [column], [another-column] FROM [table];

Counting records: SELECT COUNT([column]) FROM [table];

Counting and selecting grouped records: SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];

Selecting specific records: SELECT * FROM [table] WHERE [column] = [value]; (Selectors: <, >, !=)

Searching records for a word: SELECT * FROM [table] WHERE [column] LIKE '%[value]%';

Searching records for a word starting with [value]: SELECT * FROM [table] WHERE [column] LIKE '[value]%';

Updating records: UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];

Deleting records: DELETE FROM [table] WHERE [column] = [value];

Delete all records from a table (without dropping the table itself): DELETE FROM [table]; (This also resets the incrementing counter for auto generated columns like an id column.)

Removing table columns: ALTER TABLE [table] DROP COLUMN [column];

Deleting tables: DROP TABLE [table];

Deleting databases: DROP DATABASE [database];

Custom column output names: SELECT [column] AS [custom-column] FROM [table];

Export a database dump (more info here): mysqldump -u [username] -p [database] > db_backup.sql

Logout: exit;

Addendum

SQL joins can be tricky. Maybe this is too much for this answer, but the following chart helped me a lot, so I'll just leave it here, too :)

Please let me know in case I missed something or if there is any mistake.

Also thanks to kaiser for contributing :)