Export and import MySQL databases through command line

command line

This method works for all database sizes, including very large ones.

You must be able to log into your server with SSH.

Export

1. Log into your server via SSH.
2. Use the command cd to navigate to a directory where your user has write access. Example:
cd /var/www/vhosts/example.com/httpdocs
3. Export the database by executing the following command:

mysqldump –add-drop-table -u admin -p`cat /etc/psa/.psa.shadow` dbname > dbname.sql

Once you execute this command, you will be prompted for your database password. Type in the password and hit enter. Your database will now start exporting. When it is done, you will see the command prompt again. If it is a large database, this may take a few minutes.
NOTE:
The following variables need to be replaced with your own information:

  • -u admin specifies the database username.
  • Username is “admin” and the password is a hashed version of your Plesk admin password.
  • dbname is the name of the database you are trying to export.
  • dbname.sql is the name you want to give your backup file, and can be whatever you want.
  • Omit the –add-drop-table argument if you plan to merge this backup with an existing database when you import it. This option means the backup will totally replace the old database when it is imported.

4. You can now download the resulting SQL file. Connect to your server with FTP, navigate to the directory where you created the dump file, and download it.
5. Remove the SQL file from your web-accessible directory, if you created it in a public folder. Otherwise, anyone can download it from the web.

Import

1. Use FTP to upload your SQL file to your server. You can upload it to your default FTP directory. See Step 1 in the “Export” instructions above for another suggestion. Alternately, you can use scp to upload your file via SSH.
2. Log into your server via SSH.
3. Use the command cd to navigate into the directory where you uploaded your backup file in Step 1. If you uploaded the backup into your public htttpdocs directory, go here:

1
cd /var/www/vhosts/example.com/httpdocs/

4. Import the database by executing the following command:

1
mysql -u admin -p`cat /etc/psa/.psa.shadow` dbname < dbname.sql

OR:

1
mysql -u admin -p`cat /etc/psa/.psa.shadow` dbname -e 'source dbname.sql'

Once you execute this command, you will be prompted for your database password. Type it in and hit enter. Your database will now import. It may take a few minutes if you have a large database. When the import is done, you will be returned to the command prompt.

NOTE:

  • Variables are the same as in Step 3 from the Export section above. Please check Step 3 in the “Export” section to make sure you are correctly replacing the example code with your own information.
  • dbname.sql is the actual name of your SQL file.
  • If you have a gzipped backup of your database, you can use this line instead:gunzip < dbname.gz | mysql -u admin -p`cat /etc/psa/.psa.shadow` dbname

    You can enter in your own username, database name, and backup file name, as before. dbname.gz is the name of your gzipped backup file. Use “unzip” instead of “gunzip” for zipped files.

5. Remove the SQL file from your web-accessible directory, if you uploaded it to a public folder. Otherwise, anyone can download it from the web.

If you get an error that looks like this:

Got Error: 1045: Access denied for user ‘admin@example.com’ (using password: YES) when trying to connect

You have entered an incorrect password. Please retype it carefully, or reset your password in the AccountCenter. See How can I change my Plesk admin password? for instructions.
If you get an SQL error during the import, you can force it to finish by adding “-f” to the command, which stands for “force.” For example:

1
mysql -f -u admin -p`cat /etc/psa/.psa.shadow` dbname &lt; dbname.sql

This can help you finish an import if you have a few corrupt tables, but need to get the database as a whole imported before you do anything else.

Update WordPress URLS in Database by mysql command

When we shift a WordPress site to a new URL either live or to a production or development server, the new URL strings in the mysql database need to be changed and updated in the various mysql database tables.

With these method we can update whole mysql database rather than a WordPress export/import from within, and is best suited for a straight swap. So you would copy all the WordPress files/folders to the new destination, set the correct ownership to those files = then do the database switcheroo.

WordPress Database Switcheroo

Do a mysql database export of the old database on the old server, create a new blank database on the new server, import the old data either in phpmyadmin or mysql directly in the command line.

Make sure you have the new database selected, then run some sql updates and replacement commands on the tables notably, wp_options, wp_posts, wp_postmeta.

1
2
3
4
5
6
7
UPDATE wp_options SET option_value = replace(option_value, 'http://www.devurl', 'http://www.liveurl') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://www.devurl','http://www.liveurl');

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.devurl', 'http://www.liveurl');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.devurl','http://www.liveurl');

Finally update your WordPress config file “wp-config.php” should be in your web document root – change, databasename, username, password.

Create a Trigger in mysql


Deprecated: Function create_function() is deprecated in /home/ursaftwz/public_html/myscripthub.com/wp-content/plugins/codecolorer/lib/geshi.php on line 4698

First we need to know What is trigger:

A trigger is defined to activate when a statement inserts, updates, or deletes rows in the associated table. These row operations are trigger events. For example, rows can be inserted by INSERT or LOAD DATA statements, and an insert trigger activates for each inserted row. A trigger can be set to activate either before or after the trigger event. For example, you can have a trigger activate before each row that is inserted into a table or after each row that is updated.

Here is a simple example that associates a trigger with a table, to activate for DELETE operations.

1
2
3
4
5
6
7
DELIMITER $$
create trigger delete_user AFTER DELETE on users
FOR EACH ROW
BEGIN
Delete from address where uid=OLD.id;
END$$
DELIMITER ;

Mysql Easily insert and update records

1.Create a database.php file

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
<?php

define ('DB_SERVER', 'localhost');
define ('DB_USERNAME', 'username');
define ('DB_PASSWORD', 'password');
define ('DB_DATABASE', 'database');

$mysqli = new mysqli (DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);

if (mysqli_connect_errno()) {
trigger_error ('DB Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
exit ('Sorry! We were unable to connect to the database. Please try again.');
}

function escape_data ($data) {
global $mysqli;
if (get_magic_quotes_gpc()) $data = stripslashes($data);
return $mysqli->real_escape_string(trim($data));
}

function db_query ($query) {
global $mysqli;
if (!$result = $mysqli->query ($query)) trigger_error("Query: {$query}<br />Error: {$mysqli->error}");
return $result;
}

function db_insert ($table, $array) {
global $mysqli;
$unquote = array('NULL', 'NOW()');
foreach ($array as $key => $value) {
$columns[] = $key;
if (is_numeric($value) || in_array($value, $unquote)) {
$data[] = $value;
} else {
$data[] = "'{$value}'";
}
}
db_query ('INSERT INTO `' . $table . '` (`' . implode('`, `', $columns) . '`) VALUES (' . implode(', ', $data) . ')');
return $mysqli->insert_id;
}

function db_update ($table, $array, $column, $id, $add='') {
global $mysqli;
$unquote = array('NULL', 'NOW()');
foreach ($array as $key => $value) {
if (is_numeric($value) || in_array($value, $unquote)) {
$data[] = '`' . $key . '`=' . $value;
} else {
$data[] = '`' . $key . "`='{$value}'";
}
}
$id = (is_numeric($id)) ? $id : "'{$id}'";
db_query ("UPDATE `{$table}` SET " . implode(', ', $data) . " WHERE `{$column}`={$id} {$add}");
return $mysqli->affected_rows;
}

?>

2. include  database.php

include_once ( ‘database.php’);

2.1. Insert Query use function  

db_insert ( string $table, array $array )

1
2
3
4
5
6
$insert = array();
$insert['name'] = 'username';  //insert['table column name']='value you want insert'];
$insert['password'] = 'password';
$insert['email'] = 'email@address.com';
$insert['registered'] = 'NOW()';
$userid = db_insert('users', $insert);

2.2.Update Query use function 

db_update ( string $table, array $array, string $column, mixed $id [, string $add ] )

1
2
3
4
5
$update = array();

$ update ['email'] = 'email@address.com';

db_update ('users', $update, 'user_id', 1);

2.3.Write query use function

db_query ( string $query )

1
2
3
4
$result = db_query ("SELECT data FROM table WHERE column='{$value}'");
while (list($data) = $result->fetch_row()) {
$html .= $data . '<br />';
}

2.4.Escape any problematic characters use function

escape_data ( string $data )

1
$username = escape_data ("Hacker'); hello;--");

 

Search comma separated db column mysql

Generally, We stores various values of choices in same a column of mysql database. For example we store user’s preferred categories in user table’s preferred_categories(varchar(250)) field(column). Value stored in this field may be like 1,2,5,6,11,18 or any of similar pattern. It would be difficult to get all user whose preferred category would be “1″. If you use like query “preferred_categories LIKE ’1%’” then it also get matched with 1 & 11.

There is one of the good functions from MySQL which help to solve this problem. FIND_IN_SET() function is use to match among comma separated values. Basically FIND_IN_SET() function is use with SET type of datatype but it’s compatible to use with any other datatype where values get stored as comma separated.

mysql string function is FIND_IN_SET and its returns the position of a string value if it is available (as a substring) within a string. String contain comma separated characters or values.

This function returns 0 when search string does not exist in the string.

SYNTAX:

1
SELECT FIND_IN_SET('1',preferred_categories);

Mysql find duplicate records

Table: employee12

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
+-------+----------+--------+------------+
| Empid | Empname  | Salary | DOB        |
+-------+----------+--------+------------+
|     1 | Habib    |   2014 | 2004-12-02 |
|     2 | Karan    |   4021 | 2003-04-11 |
|     3 | Samia    |     22 | 2008-02-23 |
|     4 | Hui Ling |     25 | 2008-10-15 |
|     5 | Yumie    |     29 | 1999-01-26 |
|    10 | NULL     | 210000 | 2009-01-12 |
|    10 | jjlkl    |   2222 | 2008-02-13 |
|    10 | hkjkkjl  |   2222 | 2008-02-14 |
+-------+----------+--------+------------+

mysql> SELECT *, count(*) as n
    ->  FROM employee12
    ->  group by empid
    ->  HAVING n>1;