Archive for the MYSQL Category

Backup Single Table from a database using MySQLdump

We normally backup entire database using mysqldump utility but what if only one table gets corrupt and you  only want to  restore  that table from backup.

In that case you can  use the below simple steps to backup and restore a single mysql table using   mysqldump utility ::

Backup ::

 mysqldump -u -p databasename tablename > tablename.sql

example ::

mysqldump  psa BackupsScheduled > BackupsScheduled.sql -u admin -p`cat /etc/psa/.psa.shadow`

Restoration ::

 mysql -u -p   databasename  <  tablename.sql

for example ::

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

 

That’s all,  easy isn’t it.

 

 

DBD::mysql::st execute failed: Out of memory

If you are starting to  get the above error in the  cPanel email notification cron with  the below contents ::

 

Cron <[email protected]> /usr/bin/test -x /usr/local/cpanel/scripts/update_db_cache && /usr/local/cpanel/scripts/update_db_cache
DBD::mysql::st execute failed: Out of memory (Needed 4194248 bytes) at /usr/local/cpanel/scripts/update_db_cache line 61.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /usr/local/cpanel/scripts/update_db_cache line 63.

then don’t panic as this can simply be resolved by raising  value of “max_allowed_packet” to  128 or higher  in /etc/my.cnf file

for example ::

 

[email protected] [~]# cat /etc/my.cnf | grep max_allowed_packet
max_allowed_packet = 64M

Now increase the max_allowed_packet variable limit to  128M

 

[email protected] [~]# cat /etc/my.cnf | grep max_allowed_packet
max_allowed_packet = 128M

then finally restart the mysql service on the server

 

[email protected] [~]# /etc/init.d/mysql restart

Now, try to execute the command ::

 

/usr/bin/test -x /usr/local/cpanel/scripts/update_db_cache && /usr/local/cpanel/scripts/update_db_cache

It will  execute fine this time.

That’s all you are done.

 

Change WordPress Site URL from Shell

Sometime, it happens that when  you install WordPress on Temporary URL like http://10.10.10.1/~cPaneluser for testing and once you are done with  the testing you want to switch  the WordPress installation from a  temporary URL  to  your actual domain name http://www.domain.com.

 

But when  you try to access the WordPress installation  using the actual domain  name it’s gets redirected to the temporary URL  and you started to  wonder that how to fix this are you are no longer able to access the WordPress admin section as well.

 

Or, if you by mistake type the incorrect domain in  the WordPress Site Address (URL) and locked out yourself  of the WordPress admin  area then don’t panic , follow the below simple steps  to  regain access ::

 

Login to your  server via SSH as root user

 

[email protected][#]  mysql  -u root -p
Enter password:

Select the Wodpress database

 

mysql> use test_db;
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>

Now list all  the tables under the database test_db

 

mysql> show tables;
+----------------------------------+
| Tables_in_test_db         |
+----------------------------------+
| wp_cbnetpo_ping_optimizer        |
| wp_commentmeta                   |
| wp_comments                      |
| wp_forum_forums                  |
| wp_forum_groups                  |
| wp_forum_posts                   |
| wp_forum_threads                 |
| wp_forum_usergroup2user          |
| wp_forum_usergroups              |
| wp_links                         |
| wp_options                       |
| wp_pls                           |
| wp_postmeta                      |
| wp_posts                         |
| wp_term_relationships            |
| wp_term_taxonomy                 |
| wp_terms                         |
| wp_usermeta                      |
| wp_users                         |
| wp_wp125_ads                     |
| wp_zing_mybb_adminlog            |
| wp_zing_mybb_adminoptions        |
| wp_zing_mybb_adminsessions       |
| wp_zing_mybb_adminviews          |
| wp_zing_mybb_announcements       |
| wp_zing_mybb_attachments         |
| wp_zing_mybb_attachtypes         |
| wp_zing_mybb_awaitingactivation  |
| wp_zing_mybb_badwords            |
| wp_zing_mybb_banfilters          |
| wp_zing_mybb_banned              |
| wp_zing_mybb_calendarpermissions |
| wp_zing_mybb_calendars           |
| wp_zing_mybb_captcha             |
| wp_zing_mybb_datacache           |
| wp_zing_mybb_events              |
| wp_zing_mybb_forumpermissions    |
| wp_zing_mybb_forums              |
| wp_zing_mybb_forumsread          |
| wp_zing_mybb_forumsubscriptions  |
| wp_zing_mybb_groupleaders        |
| wp_zing_mybb_helpdocs            |
| wp_zing_mybb_helpsections        |
| wp_zing_mybb_icons               |
| wp_zing_mybb_joinrequests        |
| wp_zing_mybb_mailerrors          |
| wp_zing_mybb_maillogs            |
| wp_zing_mybb_mailqueue           |
| wp_zing_mybb_massemails          |
| wp_zing_mybb_moderatorlog        |
| wp_zing_mybb_moderators          |
| wp_zing_mybb_modtools            |
| wp_zing_mybb_mycode              |
| wp_zing_mybb_polls               |
| wp_zing_mybb_pollvotes           |
| wp_zing_mybb_posts               |
| wp_zing_mybb_privatemessages     |
| wp_zing_mybb_profilefields       |
| wp_zing_mybb_promotionlogs       |
| wp_zing_mybb_promotions          |
| wp_zing_mybb_reportedposts       |
| wp_zing_mybb_reputation          |
| wp_zing_mybb_searchlog           |
| wp_zing_mybb_sessions            |
| wp_zing_mybb_settinggroups       |
| wp_zing_mybb_settings            |
| wp_zing_mybb_smilies             |
| wp_zing_mybb_spiders             |
| wp_zing_mybb_stats               |
| wp_zing_mybb_tasklog             |
| wp_zing_mybb_tasks               |
| wp_zing_mybb_templategroups      |
| wp_zing_mybb_templates           |
| wp_zing_mybb_templatesets        |
| wp_zing_mybb_themes              |
| wp_zing_mybb_themestylesheets    |
| wp_zing_mybb_threadratings       |
| wp_zing_mybb_threads             |
| wp_zing_mybb_threadsread         |
| wp_zing_mybb_threadsubscriptions |
| wp_zing_mybb_threadviews         |
| wp_zing_mybb_userfields          |
| wp_zing_mybb_usergroups          |
| wp_zing_mybb_users               |
| wp_zing_mybb_usertitles          |
| wp_zing_mybb_warninglevels       |
| wp_zing_mybb_warnings            |
| wp_zing_mybb_warningtypes        |
+----------------------------------+
88 rows in set (0.00 sec)
mysql> select * from wp_options where option_value = 'http://www.testdomain.com';
+-----------+---------+-------------+--------------------------+----------+
| option_id | blog_id | option_name | option_value             | autoload |
+-----------+---------+-------------+--------------------------+----------+
|         2 |       0 | siteurl     | http://www.testdomain.com | yes      |
|        39 |       0 | home        | http://www.testdomain.com | yes      |
+-----------+---------+-------------+--------------------------+----------+
2 rows in set (0.00 sec)

Update the new site URL from  http://www.testdomain.com to http://www.domain.com

 

mysql> update wp_options set option_value = 'http://www.domain.com' where option_id = 2;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1 Changed: 1 Warnings: 0
mysql> update wp_options set option_value = 'http://www.domain.com' where option_id = 39;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1 Changed: 1 Warnings: 0
mysql> exit
 Bye

That’s all ,  you will  now be able to access the WordPress with  the actual domain.com .