Archive

Archive for the ‘MySQL’ Category

String replace in MySQL

December 4th, 2013 No comments

Change a part of text in a MySQL database, can execute with the command REPLACE().

Example: change HTML bold text in the ‘description’ field that uses B tag with STRONG tag.

UPDATE texts SET description = REPLACE(description, ‘B>’, ‘STRONG>’);

Set timezone from MySQL

July 20th, 2013 No comments

Date and time within MySQL, you can check with NOW() (MySQL does not distinguish between uppercase and lowercase, so you can write now()). An example would be:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-07-20 11:11:11 | 
+---------------------+
1 row in set (0.00 sec)

If the time differs from the time in your time zone, use the following command to set the appropriate timezone:

SET GLOBAL time_zone = '+1:00';
Categories: MySQL Tags: ,

Copy Complete MySQL database

February 22nd, 2013 No comments

Here are simple ways you can make a copy of a base in MySQL. It is essential that you have access to the command line.

        

  • create a database in which you want to transfer data via phpMyAdmin or a similar tool
        

  • From the console, execute the following command:

    mysqldump -h [server] -u [user] -p[password] db1 | mysql -h [server] -u [user] -p[password] db2
    

    Note that between -p and [password] no spaces.

    If the command is running in the system that is not password protected (typically servers on the local computer) parameter with a password can be omitted. These commands can be executed in two steps as follows:

    mysqldump -h [server] -u [user] -p[password] db1 > dump.sql
    mysql -h [server] -u [user] -p[password] db2 < dump.sql
    

    In this way he created mysqldump file that can be saved as a backup.

Categories: MySQL Tags: , ,