#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2004
    Posts
    597
    Rep Power
    15

    About migrating MySQL etc


    Hi all,

    1)
    OS Ubuntu 12.04 desktop 64bit
    2)
    OS Fedora 18 desktop 64bit

    migrating MySQL from 1) to 2) same version

    I found many suggestions on Internet re migrating database and unable to ascertain the easiest way on its migration. Please advise. TIA

    Besides on running following commands;

    mysql> SHOW databases;
    Code:
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | aaa	             |
    | bbb etc            |
    +--------------------+
    mysql> USE aaa;

    mysql> SHOW tables;
    Code:
    +-----------------------+
    | Tables_in_aaa         |
    +-----------------------+
    | 111		        |
    | 222	                |
    | 333		        |
    | 444 etc	        |
    +-----------------------+
    mysql> SELECT * FROM aaa;
    Code:
    +----+-------------+---------------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+--------------+-------------+----------------+-------------+---------------+------------------
    Whether above indicates that there is no data/entry on the database?

    mysql> SHOW columns from 222;
    Code:
    +-----------------------+---------------------+------+-----+---------------------+----------------+
    | Field                 | Type                | Null | Key | Default             | Extra          |
    +-----------------------+---------------------+------+-----+---------------------+----------------+
    | ID                    | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
    | xxx                   | bigint(20) unsigned | NO   | MUL | 0                   |                |
    | yyy                   | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
    | zzz etc               | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
    +-----------------------+---------------------+------+-----+---------------------+----------------+
    What command shall I run to display the columns and their content in readable form?

    Thanks

    SL
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Originally Posted by satimis
    mysql> SELECT * FROM aaa;
    Code:
    +----+-------------+---------------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+--------------+-------------+----------------+-------------+---------------+------------------
    Whether above indicates that there is no data/entry on the database?

    ...

    What command shall I run to display the columns and their content in readable form?
    I think that it indicates that you only copied the last row in the CLI window, which is the end of the column formatting.

    Because if the table is empty I get this:
    Code:
    mysql> select * from users_info;
    Empty set (0.02 sec)
    While if I have data in the table it looks like this:
    Code:
    mysql> select * from mytable;
    +----+
    | id |
    +----+
    |  1 |
    |  2 |
    |  3 |
    |  4 |
    |  5 |
    |  6 |
    |  7 |
    |  8 |
    |  9 |
    | 10 |
    | 11 |
    | 12 |
    | 13 |
    | 14 |
    | 15 |
    +----+
    15 rows in set (0.00 sec)
    /Stefan
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2004
    Posts
    597
    Rep Power
    15
    Originally Posted by sr
    I think that it indicates that you only copied the last row in the CLI window, which is the end of the column formatting.

    Because if the table is empty I get this:
    Code:
    mysql> select * from users_info;
    Empty set (0.02 sec)
    While if I have data in the table it looks like this:
    Code:
    mysql> select * from mytable;
    +----+
    | id |
    +----+
    |  1 |
    |  2 |
    |  3 |
    |  4 |
    |  5 |
    |  6 |
    |  7 |
    |  8 |
    |  9 |
    | 10 |
    | 11 |
    | 12 |
    | 13 |
    | 14 |
    | 15 |
    +----+
    15 rows in set (0.00 sec)
    Hi Stefan,

    Thanks for your advice.

    I display all the output here;

    mysql> SHOW databases;
    Code:
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | wordpress          |
    +--------------------+
    5 rows in set (0.01 sec)
    mysql> USE wordpress;
    Code:
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> SHOW tables;
    Code:
    +-----------------------+
    | Tables_in_wordpress   |
    +-----------------------+
    | wp_commentmeta        |
    | wp_comments           |
    | wp_links              |
    | wp_options            |
    | wp_postmeta           |
    | wp_posts              |
    | wp_term_relationships |
    | wp_term_taxonomy      |
    | wp_terms              |
    | wp_usermeta           |
    | wp_users              |
    +-----------------------+
    11 rows in set (0.00 sec)
    mysql> SELECT * FROM wp_posts into outfile "/tmp/wp-post";
    Code:
    Query OK, 24 rows affected (0.00 sec)
    I got its content as a text file.

    Now what command shall I execute to find "http://www.aaa.com/data/" and replace it with "http://www.bbb.com/data" on wp_posts ?

    Thanks


    Edit:

    Can following command work for me?
    Code:
    UPDATE wp_posts SET POST_CONTENT = replace(POST_CONTENT, 'www.aaa.com', 'www.bbb.com');
    Rgds
    satimis
    Last edited by satimis; March 13th, 2013 at 09:50 AM.

IMN logo majestic logo threadwatch logo seochat tools logo