Category: MySQL Toggle Comment Threads | Keyboard Shortcuts

MySQL is a complex topic – I know enough to get by in the world of WordPress. It’s one of those topics that you never knew you needed until you started diving deeper into storing data.

I like to pronounce it “My Ess Queue Elle”, not “My Sequel”…
and here are some other pointers I’ve picked up along my journey.

  • Jay Versluis 6:24 pm on September 24, 2012 Permalink | Reply  
    Categories: MySQL, WordPress ( 19 )

    How to move very large MySQL Databases 

    I’ve been shifting some things around and came across several very large WordPress databases for a few projects I was hosting. The largest was 1.3GB or 317MB compressed (!) which obviously poses a problem. Most sites – even those of frequent bloggers – amount to well under 2MB when compressed, but Multi Site or BuddyPress databases can get large quick.

    To extract a database from a WordPress site, I’ve always relied on the superb WP DB Backup by Austin Matzko but even that timed out after 170MB.

    In this article I’ll talk you through how we can handle this. I’m assuming here that a simple phpMyAdmin export/import isn’t working for you anymore. This is by all means not the only way to go about this task.

    (More …)





     
    • ox 6:42 pm on September 24, 2012 Permalink | Reply

      “Hope this article helps…” — nope, useless, I don’t have a Mac.

      • Jay Versluis 7:02 pm on September 24, 2012 Permalink | Reply

        You sad sad man (or woman). I suggest you stop whining and get a Mac.

    • Elliott Richmond 7:29 am on September 26, 2012 Permalink | Reply

      SequelPro rocks!! But you can still use it to upload to the new server as well, no need for BigDump unless I’m missing something here?

      • Jay Versluis 9:35 am on September 26, 2012 Permalink | Reply

        You can??? That would be absolutely awesome! I didn’t find an import feature – could you describe where it is?

        • Alessio 5:08 am on January 3, 2014 Permalink | Reply

          I’ve done a copy/paste of SQL queries with a 70mb (uncompressed) db, so very small compared with your, and SequelPRO performs around 1500 queries in less than 10 minutes… this was my n00b way πŸ™‚

    • Stephen 10:48 am on February 26, 2013 Permalink | Reply

      Great stuff! Very informative, thanks for having this blog out there, very helpful for WordPress.org developers!

    • Daitya 10:30 pm on September 9, 2013 Permalink | Reply

      Jay, I’ve got a wp multisite installation with 75 tables and 72 MB on a shared hosting account running Cloud Linux, and cPanel’s phpMyAdmin is just not handling backup any longer. Host doesn’t allow shell access on shared hosting accounts, and switching over to VPS or dedicated hosting is out of budget. Would you recommend using phpMyAdmin to download the db in parts (say, x number of tables in one download, and y number of tables in another)? Can these be stitched together when populating a new database as in restore or dev work? How would this work?

      • Jay Versluis 10:51 pm on September 9, 2013 Permalink | Reply

        Hi Daitya, yes that does sound a bit large for phpMyAdmin to handle. My two cents: Splitting the database into chunks could work, I’d try exporting it table by table – you can probably group them together, and only export the largest one separately (however you may find that one table takes up 90% of your total size, probably the posts table). It’s trial and error with phpMyAdmin I’m afraid. I would not recommend breaking up tables though – it can get extremely messy.

        A much better and more convenient way I’d say is Sequel Pro. It’s a free Mac app that lets you administer your database remotely, so you can read it out right on your desktop and export it that way: http://www.sequelpro.com. This will also allow you to conveniently import large databases. I swear by it!

        Another idea to get round the shell access would be to access your MySQL server from another Linux machine, and issue a mysqldump command from there. Your “local” Linux system would then access the remote one. Let me know if this makes sense.

    • http://jordimundo.com 2:07 pm on June 8, 2014 Permalink | Reply

      sequelYOG on PC, there is a free version, and it is awesome, actually I’d say it is better than sequel pro, you can copy DB across hosts straight for example…

      • Jay Versluis 2:24 pm on June 8, 2014 Permalink | Reply

        Thanks Jordi, awesome tip – I was always wondering if there’s a similar tool on Windows. I’ll check it out – thanks for sharing!

    • Sam 10:17 am on March 12, 2015 Permalink | Reply

      Sequel pro has import as well under the File menu. So if your hosts gives you the permission, then you can do a direct import.

    • Dubai jobs 4:18 am on March 25, 2016 Permalink | Reply

      great topic
      but i think the best way is using config.php

      • Jay Versluis 9:54 am on March 25, 2016 Permalink | Reply

        Please elaborate what you mean by config.php.

  • Jay Versluis 11:09 pm on June 16, 2012 Permalink | Reply  
    Categories: MySQL ( 19 )

    How to change your MySQL User Password 

    Today I had to admit that I did not remember the password for a MySQL user account. Since it’s saved in encrypted form simply reading it out won’t mean I can recover it. A Google search did eventually bring up the correct way of doing it, however it took me quite some time – so here’s how you can do this:

    (More …)





     
  • Jay Versluis 4:21 pm on April 22, 2012 Permalink | Reply
    Tags: , ,   

    Categories: Linux, MySQL ( 96 )

    How to install MySQL on CentOS 

    This is a step-by-step guide of what you need to do in order to install and setup MySQL on a new server.

    We’ll prepare a fresh CentOS 6 system (64 bit) for use as a database server. All you need is access to an SSH client and your server root credentials.

    (More …)





     
    • Jeff Huckaby 2:27 pm on June 20, 2012 Permalink | Reply

      Another item I always do is update the default my.cnf. By default, a number of features, such as query caching, is not enabled.

      If you look in:
      /usr/share/doc/mysql-server-5.0.95/

      You will find some example my.cnf files. I typically use my-large.cnf as a starting point for systems with 4GB of RAM or less. Use my-huge.cnf for systems with more than this.

      Once deployed, check out tools like http://mysqltuner.pl and http://hackmysql.com/mysqlreport to better optimize your installation.

      • Jay Versluis 10:41 pm on June 21, 2012 Permalink | Reply

        Thank you so much for sharing Jeff, much appreciated! I’ll go check it out πŸ˜‰

    • wayne 12:01 pm on September 2, 2012 Permalink | Reply

      hi, i am trying to set up mysql, and i still don’t see how to get to the actual webpage to set up everything. i set everything up right as far as i know. but i still need help getting to the actual program

    • gunawan 7:20 am on October 1, 2012 Permalink | Reply

      I tried but install but after click the yum
      service ….
      is failed
      then I try
      mysql
      “error 2002 …”

      what I miss here?

    • Tom 4:30 am on December 24, 2012 Permalink | Reply

      It doesn’t work with me.
      With no configuration, i think it’s impossible

      • Jay Versluis 4:12 pm on December 24, 2012 Permalink | Reply

        What’s the error message you’re getting? Is yum installed on your system?

    • booksd 9:46 am on January 11, 2013 Permalink | Reply

      Any suggestions for the following command and response?

      [root@LiunxCentos arasu]# service mysql start
      mysql: unrecognized service
      [root@LiunxCentos arasu]#

      • Jay Versluis 4:06 pm on January 11, 2013 Permalink | Reply

        Sounds like MySQL is not installed, or your system does not recognize the service command. You can also try this:

        /etc/init.d/mysqld restart

        If you get a similar error message, then MySQL is not installed.

      • Jay Versluis 4:08 pm on January 11, 2013 Permalink | Reply

        I just re-read your command – the service is called mysqld, not mysql. Try again using

        service mysqld restart

        • Yeah 9:44 pm on January 11, 2013 Permalink | Reply

          Yeah, you actually wrote ‘service mysql restart’ in the tutorial, I imagine its tripped up a few others, myself included. Only took me 30 secs on google to figure out its ‘service mysqld restart’..Good article anwyays, thanks

          • Jay Versluis 12:41 am on January 12, 2013 Permalink

            How embarrassing!! I’ll change that right away – thanks for letting me know πŸ˜‰

          • Carl Partridge 10:40 am on May 3, 2013 Permalink

            Actually, don’t change it – on some installations the service is called mysqld, on some the service is called mysql

            If you install from the current packages as per your tutorial, the service is called mysql

    • Mihai Alexandru 6:51 pm on February 23, 2013 Permalink | Reply

      Go on http://softiny.com and choose the LINUX tab and there are software apps to help you do this…

    • Grover 11:38 pm on March 25, 2013 Permalink | Reply

    • Independent Software Developer 4:14 pm on March 26, 2013 Permalink | Reply

      My first time really getting into CentOS and I’m trying to setup basic hosting for my site… I now have a new found respect for system admins because I have gotten so many headaches its not even funny.

      Thanks for this tutorial!

    • madhu 11:31 pm on January 25, 2014 Permalink | Reply

      hai frnds. . . ..
      iam facing problem with secure installation of mysql in centos 6.5
      while secure installation of mysql : /usr/bin/mysql_secure_installation
      am getting error as:Enter current password for root (enter for none):
      ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
      am entered as enter button only,i got the above error

      • Jay Versluis 11:48 pm on January 25, 2014 Permalink | Reply

        Try setting a password via the mysqladmin command as described by the friendly install message. Also, check if MySQL is actually running – you can’t connect if the service is down.

  • Jay Versluis 12:42 pm on January 28, 2012 Permalink | Reply
    Tags:   

    Categories: MySQL, WordPress ( 19 )

    How to deploy HyperDB 

    Ever since I’ve started experimenting with HyperDB I thought that once I’ve got enough servers at my disposal, and if I ever figure out how to setup MySQL replication I’d bring the two together. I believe that day has finally come: HyperDB is now deployed across over 60 sites I’m taking care of.

    I want to share with you my configuration and some of the pitfalls I’ve come across – if it helps, great. But mainly these notes are for me to remember how I did it when the next cluster needs to be built.

    (More …)





     
    • Jay Versluis 6:19 pm on February 6, 2012 Permalink | Reply

      UPDATE:

      It’s been three weeks since I’ve had HyperDB running in the above sample configuration on about 6 test sites. The concept works, albeit with minor performance decreases over a standard dual-server setup. However I’ve run into a couple of problems twice over the three weeks: MySQL database inconsistencies.

      MySQL just crashes every once in a while. Sad but true – and I don’t know why, I’m just the observer here. And it’s no biggie either, you just go and reboot the service. This means that one of the slaves takes about a minute to catch up with the master. No problem here either.

      But as soon as one server is out of sync with the others the real nightmare begins: say server 1 recorded a change before server 2 crashes. Server 2 reboots and applies the changes one cycle too late so he misses creating a database table for example. Now you delete said table on server 1… Server 2 tries to apply this change, finds that this table doesn’t exist and throws out an error – and by the looks of it stops replicating altogether. That’s extremely ungood!

      It means you have to play catch-up with every server and set the master log coordinates manually again. This can get ugly, and I had to do this twice. I’m sure there’s another way to avoid this, but sadly I don’t know how at this moment.

      There is a project which may help called MMM (or MySQL Multi Master Replication Manager): http://mysql-mmm.org/ – looks extremely complicated to setup so I’m going to leave this for another long winter night.

    • Tung 6:46 am on July 10, 2013 Permalink | Reply

      Hi, why do you 2 commands to add Master 1 for write queries and read queries? Why don’t use combine them into 1 command with (read=>1, write=>1) instead of (read=>1, write=>0) and (read=>0, write=>1)

      • Jay Versluis 8:18 am on July 11, 2013 Permalink | Reply

        Both work fine for the example, but in a production environment it is plausible to have a master purely for writes and never for reads (and let the slaves do that) hence I split it up. It’s just for clarity really.

  • Jay Versluis 8:41 am on July 6, 2010 Permalink | Reply  
    Categories: How To, MySQL, PHP ( 30 )

    How to increase the Upload Limit in phpMyAdmin? 

    Since phpMyAdmin is written in php, all we need to do is change the file upload limit in the php.ini file. On CentOS and RHEL distributions, this file is located in /etc/php.ini

    Find this section:


    ;;;;;;;;;;;;;;;;
    ; File Uploads ;
    ;;;;;;;;;;;;;;;;
    ; Whether to allow HTTP file uploads.
    file_uploads = On
    ; Temporary directory for HTTP uploaded files (will use system default if not
    ; specified).
    ;upload_tmp_dir =
    ; Maximum allowed size for uploaded files.
    upload_max_filesize = 2M

    Change the 2M to something bigger, say 100M.

    Please note that this is a server wide setting and as such will affect all users on your server.





     
    • Carlos 12:09 pm on March 4, 2013 Permalink | Reply

      Where’s the phpmyadmin.ini file?

  • Jay Versluis 10:39 am on May 3, 2010 Permalink | Reply  
    Categories: How To, MySQL ( 30 )

    Where are the MySQL data files on CentOS? 

    They’re located in

    /var/lib/mysql/

    Each database has a subfolder here. You’ll also find the MySQL log files here.





     
  • Jay Versluis 7:22 pm on January 5, 2010 Permalink | Reply  
    Categories: Linux, MySQL ( 96 )

    How to repair MySQL tables from the Command Line 

    Sometimes your tables crash and your website is unresponsive. You don’t have access to phpMyAdmin to do a repair, because you can’t access Plesk. That sucks!

    When you’re out in the field and all that’s at hand is a trusty SSH connection from your mobile, here’s an alternative way to bring your WordPress Sites back to life.

    You need the following ingredients:

    • Database Name (I’m using “databasename” here)
    • User Name (I’m using “username” here)
    • Password for the above

    All three will be in your wp-admin.php file, use Vi to check if you’ve forgotten them.

    Next it’s time to get busy with some MySQL commands I keep forgetting (hence this article):

    mysql -u username -p

    This will ask you for your password, which you can type but won’t see written out onscreen. Upon success, you’ll be deep in MySQL territory, not the Linux prompt anymore. Next, we’ll select our actual database in use for the site:

    use databasename;

    Note the importance of the semicolon at the end of the line. Now let’s see which tables are in that database and what they’re called:

    show tables;

    Excellent! We’re getting there! You’ll see everything that’s in your database, almost like in phpMyAdmin (just less convenient). By default, you should be seeing entries like “wp_options”, “wp_links”, wp_posts” and so forth. The “wp_” is the default prefix for WordPress tables, but depending on your installation this may have changed to something else.

    Depending on what else you’re using your database for, there may well be other entries from projects such as PHPList, Drupal, phpBB and so forth. The principles are applicable for every application that’s using MySQL.

    You’ve got a whole host of commands at your fingertips now, but the most important ones for a rescue mission like this are probably

    check table yourtablename;
    repair table yourtablename;

    The “check table” command seems to repair minor errors, like hosts not having closed connections. It also shows you what’s wrong with a table, or if it’s OK. The “repair” command checks, optimizes and repairs a table – so it’s probably the best one to use.

    Unlike phpMyAdmin, you have to issue one command per table; you can’t select all tables and say “repair all” (and if you can, I don’t know about it – in which case, please leave a comment and enlighten us).

    For WordPress, that’s 11 tables you need to check – so it’s not a major inconvenience.

    Once you’re ready to leave the heady heights of MySQL, type “quit” and you’re back to the command prompt.

    Good Luck!





     
    • Jay 5:54 am on December 28, 2010 Permalink | Reply

      Some of my tables in my chat room db crashed. I was given the following command to repair all tables at once. The developer of my chat software gave it to me and said it worked.

      mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

      It didn’t work. It may work with phpadmin but using the mysql command prompt it doesn’t work.

      The good news for me is that your commands worked great and fixed my chat room db problems.

      Thanks a bunch.

      • Jay Versluis 12:07 pm on December 28, 2010 Permalink | Reply

        I’m glad to hear it!

        The command you’ve mentioned probably doesn’t work because there may be a password associated with the root user. Say if your password is “jeronimo” then the command needs to look like this – notice the absence of a space in between “-p” and “jeronimo”:

        mysqlcheck -u root -pjeronimo –auto-repair –check –optimize –all-databases

        Thanks for sharing πŸ˜‰

        • Robert 7:29 am on December 9, 2012 Permalink | Reply

          The Issues with the command is it needs to be linke this

          mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

          Note the Double dash

    • Julio Saenz 12:37 am on February 20, 2012 Permalink | Reply

      hi all i disliked phpMyAdmin’s interface so i decided i would built a new MySQL manager like the MySQL query browser. please if you have a change can you give it a try
      github ->https://github.com/sucaritas/MyXQuery
      LIVE DEMO -> http://www.farfile.com/Demos/MyXQuery/
      it works like the MySQL query Browser (ctrl+enter to execute a query)
      you can right click on the tree for other things too.

      • Jay Versluis 7:36 pm on February 21, 2012 Permalink | Reply

        Thanks Julio,

        I’ll take a look at it. May well come in handy πŸ˜‰

    • jay 11:14 am on April 12, 2012 Permalink | Reply

      Awesome article. Thanks very much WP guru

    • Frank Thomas 3:02 am on May 1, 2012 Permalink | Reply

      Excellent post. Nope, this isn’t spamm-o-rama, but a sincere thanks. I did have to ensure that I put a — in front of all the mysqlcheck options command. I was running over 100% cpu (didn’t know that that was possible, lol), but it was. Seems to have help!

      Thanks, Frank

    • Jacer Omri 12:56 am on December 7, 2012 Permalink | Reply

      you can repair all your databases using:
      mysqlcheck –repair –use-frm –all-databases -u username -p

    • Bangladesh Result 1:40 pm on December 14, 2012 Permalink | Reply

      Thank you! Though its very basic, i need some advanced SQL query!

    • Robert 8:25 pm on December 15, 2012 Permalink | Reply

      mysqlcheck –repair –use-frm –all-databases -u username -p

      fixed My databases better than the other querries

    • Sakvith 12:29 pm on March 16, 2013 Permalink | Reply

      Thanx it worked

    • Luis Pantoja Vera 1:00 pm on November 12, 2015 Permalink | Reply

      thanks!!!, the commands listed here worked for my db.

    • Manish 6:02 am on November 25, 2015 Permalink | Reply

      Thanx it worked.. you rocks… πŸ™‚

  • Jay Versluis 11:08 am on February 25, 2009 Permalink | Reply
    Tags: , ,   

    Categories: MySQL ( 19 )

    How to restore your database? 

    The Database is the heart of your WordPress installation. Simply backing up and restoring all your files via FTP is not enough – it only contains the actual WordPress “programme” if you will, your plugins and themes, and all your uploaded media files (like pictures). It does NOT however contain your posts, links and some of the configuration data. That’s where the MySQL Database comes into play.

    For any of these articles to make sense, you need to be familiar and have access to phpMyAdmin. Your hosting provider will gladly help you our there.

    If you have a fairly small database (up to 5MB), have a look at this:

    If you database is significantly larger, you need other means of uploading and importing it. I reccomend the sue of a PHP script, as detailed here:

    One sure PHP Script is BigDump:

    Obviously, before a RESTORE actually works, you would have had to BACKUP your database (naturally). Perhaps you would like to check out this article to find out how to do this:

    http://wordpress-guru.co.uk/archives/how-to-restore-your-databasehow-to-backup-your-database/





     
  • Jay Versluis 11:01 am on February 25, 2009 Permalink | Reply  
    Categories: Domains and Hosting, MySQL ( 4 )

    How to move WordPress from one server to another 

    It’s not as difficult as one might think, as this article explains:

    http://codex.wordpress.org/Moving_WordPress

    If however this means not a lot to you, or you have tried and it’s still not working, you should check out the following topics:

    Hope this helps πŸ˜‰





     
c
compose new post
j
next post/next comment
k
previous post/previous comment
r
reply
e
edit
o
show/hide comments
t
go to top
l
go to login
h
show/hide help
shift + esc
cancel