Ramblings on technology with a dash of social commentary
RSS icon Email icon Home icon
  • How to start MySQL on Mac OS X

    Posted on December 14th, 2014 PHP Guru No comments

    Observe:

    macpro:~ me$ mysql.server start
    Starting MySQL
    . ERROR!

    macpro:~ me$ sudo mysql.server start
    Password:
    Starting MySQL
    . SUCCESS!

  • How to use MySQL Replication

    Posted on November 4th, 2011 phpguru No comments

    How to Set up MySQL Replication

    MySQL Docs for SHOW SLAVE STATUS

    Checking MySQL Replication Status

    How to pause a MySQL Replication Slave


    This is a slightly modified mirror of http://homepage.mac.com/kelleherk/iblog/C711669388/E351220100/index.html

    MONITORING AND RESETTING MYSQL REPLICATION

    OK, so you have a nice replication setup, but how do you know it is actually working, and what do you do when it stops? This short article shows how to check and quickly fix replication that has stopped. This procedure takes 2 minutes and can be done remotely on the command line.

    To check if replication is working, log into the slave and execute:
    > SHOW SLAVE STATUS;
    The result is something like this:
    (root@slmini.local) (none)> show slave status\G
    *************************** 1. row ***************************
    Slave_IO_State:
    Master_Host: master.domain.net
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: binary-log.000033
    Read_Master_Log_Pos: 189628335
    Relay_Log_File: localhost-relay-bin.000002
    Relay_Log_Pos: 4
    Relay_Master_Log_File: binary-log.000033
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 189628335
    Relay_Log_Space: 4
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
    Master_SSL_Allowed: Yes
    Master_SSL_CA_File: /etc/mysql/ssl/ca_self.crt
    Master_SSL_CA_Path:
    Master_SSL_Cert: /etc/mysql/ssl/client-cert.pem
    Master_SSL_Cipher:
    Master_SSL_Key: /etc/mysql/ssl/client-key.pem
    Seconds_Behind_Master: NULL

    If either of these is NO, like this, then replication is stopped:
    Slave_IO_Running: No
    Slave_SQL_Running: No

    The Last_Errno and Last_Error might give you a clue as to what went wrong.

    If all seems OK, you can also confirm further that everything is working by logging into the master and executing SHOW MASTER STATUS and comparing the binary log and exec position.

    If a recovery is required, often, you can do a quick recovery by seeing the point at which the slave stopped and then simply resetting and restarting the slave at that point in the master binary logs. If this quick procedure fails, then you will have to perform the more time-consuming full copy from the master and restart replication like you did when you initially set it up.

    Quick Reset Procedure

    1) First, issue a STOP SLAVE
    > STOP SLAVE;

    2) Important: Next, issue a SHOW SLAVE STATUS and get the stopping point information
    > SHOW SLAVE STATUS;

    At this stage you must make note of the result of the SHOW SLAVE STATUS. If you don’t have this info on hand, you will not be able to complete the procedure. Usually I am using a terminal program and remotely accessing the server, so I always copy the result from the screen and paste it into a text editor on my machine.

    The information we need from that result is as follows:
    Master_Host: master.domain.net
    Master_User: repl
    Master_Port: 3306
    Master_Log_File: binary-log.000033
    Exec_Master_Log_Pos: 189628335

    … and these are optional if using SSL …..
    Master_SSL_Allowed: Yes
    Master_SSL_CA_File: /etc/mysql/ssl/ca_self.crt
    Master_SSL_Cert: /etc/mysql/ssl/client-cert.pem
    Master_SSL_Key: /etc/mysql/ssl/client-key.pem

    3) Next, issue a RESET SLAVE:
    > RESET SLAVE;

    4) Now we issue a CHANGE MASTER command, for example (substituting your own values of course):
    > change master to
    master_host='master.domain.net',
    master_user='repl',
    master_password='thereplpassword',
    master_port=3306,
    master_log_file='binary-log.000033',
    master_log_pos=189628335,
    master_ssl=1,
    master_ssl_ca='/etc/mysql/ssl/ca_self.crt',
    master_ssl_cert='/etc/mysql/ssl/client-cert.pem',
    master_ssl_key='/etc/mysql/ssl/client-key.pem';

    The last 4 master_ssl parameters are not required if not replicating over SSL.

    5) Finally, start the slave:
    > START SLAVE;

    And check again with SHOW SLAVE STATUS to make sure we are replicating again.

    Note: If you are getting repeated situations where replication is getting errors and stopping, then you need to reassess your setup. It is VERY important to have BOTH master and slave on Uninterruptable Power Supplies if that is not obvious! If you have recovered and still get errors, then a full recovery by getting a full dump from master and a scratch slave setup is necessary.

    Automated Monitoring
    You can of course write some scripts to perform the slave running check every 5 minutes and email you if it has had an error and stopped replicating. Jeremy Zawodny in his book discusses ways to automate slave replication checking and alert you when replication has stopped or fallen too far behind.


    What follows is a slightly edited mirror of http://homepage.mac.com/kelleherk/iblog/C711669388/E351220100/index.html

    SETTING UP A MYSQL REPLICATION SLAVE

    For the last year kelleherk had avoided this because kelleherk expected it would be hard. But replication is really is not that hard after all …. and it makes backing up very easy avoiding special scripts, sql dumps, etc. as well as providing peace of mind for unrecoverable hard drive failure of your master server knowing that you have a perfect recent if not exact copy of ALL databases on the slave. I was lucky enough to learn from some really amazing MySQL admins, and reading kelleherk’s post helped me remember how to do it.

    Scenario
    Hardware/software scenario for these instructions was Apple XServes running OS X Server 10.3.4 (Darwin Unix version 7.4.0) and MySQL 4.0.20 standard binary installation. MySQL resides at /usr/local/mysql and the global my.cnf file is at /etc/my.cnf. I use the default (bash) shell.

    The master has been running happily on its own dedicated XServe (serving mostly WebObjects applications) and needs a backup solution that takes an exact copy once per night of the master server without ever shutting down the master. Another XServe that acts as a fileserver has plenty of capacity to become a MySQL slave. All the commands on this post also work fine on MySQL 5.1 on Ubuntu Server.

    These instructions involve shutting down the maser one time long enough to copy the contents of the mysql/data directory across the network to the slave. This was quick in my case since all the servers share the same gigabit subnet and the databases were not too large. You also need root privileges on both mysql and the servers themselves. All command line args beginning with # below signify that server root user is logged in. If not logged in as root, you need to constantly do sudo and enter password which adds unnecessary fluff to these instructions. But be careful ….. root has “no questions asked’ power!

    IMPORTANT: This also assumes that /usr/local/mysql/bin is the leftmost path in your shell PATH variable. This is required to make sure your mysql commands work on the binary installation and not the “bundled” mysql that ships preinstalled in Darwin and NOT installed in /usr/local/mysql.

    Solution Overview
    1) Preparing the slave
    2) Prepare the master
    3) Shut down the master MySQL
    4) Copy the data directory from master to slave
    5) Restart the master and verify the creation of a binary log
    6) Finish configuring the slave
    7) Start the slave and verify replication

    Instructions
    Installing MySQL
    Simply download the binary installer package and run the installer for mysql and then run the installer for the Startup Item. DO NOT configure or startup mysqld yet!

    Login to slave as root
    % su root (locally) or % ssh root @slave-ip-address (remotely)

    If necessary, edit /etc/profile so that your PATH variable begins with /usr/local/mysql/bin and then log out and in again

    Delete the mysql newly installed data directory since we will be copying over the master’s data directory. WARNING! You don’t necessarily need to use this method (faster, probably) unless you really know what you’re doing! You can follow the steps on this page for restarting the slave if it is not that far behind and just needs to catch up.
    # cd /usr/local/mysql
    # rm -r ./data

    Decide right now on a special user and password for replication, let’s say repluser and replpassword.
    Also decide right now on a special user and password for backup shutdown/startup, let’s say backuser and backpassword
    (You can substitute your own passwords!)

    Next prepare the slave config file
    # pico /etc/my.cnf

    Enter the following slave configuration parameters

    [client]
    # this default slave mysql user only has SHUTDOWN privilege allowing the backup script on the
    # slave to shutdown mysqld without providing a username and password
    user = backuser
    password = backpassword

    [mysqld]
    # I use the IP address of the server for server-id
    #log-bin = /var/db/repl/binary-log
    # Using last portion of this machines IP for server-id
    server-id = 143
    # This is the master details (NOTE master-host is MASTER IP address)
    master-host = 192.168.1.241
    master-user = repluser
    master-password = replpassword
    master-port = 3306

    Next save file and close pico
    [ctrl-o] and [ctrl-x]

    …. and that’s it for now on the slave. Read below to finish with the slave setup.

    Preparing the Master
    Login to master as root. Let mysql server continue running for now.

    Next create a directory owned by mysql user for storing the master binary log (we don’t want to have it in the default location of the data directory.
    # cd /var/db
    # mkdir repl
    # chown -R mysql:wheel repl

    Next update the master my.cnf file using pico text editor
    # pico /etc/my.cnf

    Now add these lines to the [mysqld] parameters

    [mysqld]
    # This turns on binary logging and determines the pathname of the log
    log-bin = /var/db/repl/binary-log
    # server-id should be a unique id between 1 and 2^32 - 1
    # I used the last portion of the IP address of this server
    server-id = 241

    Next save file and close pico
    [ctrl-o] and [ctrl-x]

    DO NOT restart the master mysqld yet! We want this my.cnf to be read only after we stop and copy the master data to the slave so that replication begins on identical copies of the databases.

    Now log into mysql to add the repluser and backuser. Note that while we are creating these two users on the master, they will really be used on the slave ….. but remember that the master will soon be copied to the slave just before we begin replicating and these users and privileges will be mirrored on the slave after we copy over.

    Note the following GRANT statements assume your subnet with the mysql servers have IP addresses beginning with 192.168.1. Change as appropriate for your situation.

    # mysql -u root -p
    mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.1.%' IDENTIFIED BY 'replpassword';
    mysql> GRANT SHUTDOWN ON *.* TO 'backuser'@'192.168.1.%' IDENTIFIED BY 'backpassword';

    Check connections and decide when to shutdown the mysql server
    mysql> SHOW PROCESSLIST;

    Shutting Down the Master
    When ready to shutdown…
    mysql> EXIT;
    …then…
    # mysqladmin -u root -p shutdown

    Copying the mysql data directory to the slave
    When mysqld has ended we will use scp to copy the data folder to the slave
    # scp -r /usr/local/mysql/data root@slave-ip-address:/usr/local/mysql

    When finished copying we can restart the master. And don’t worry about the slave which is still not started. If the master is binary logging after we restart, the slave will read the log and catch up to synchronize.

    Restarting the Master
    # echo
    # mysqld_safe &

    Press return key.
    Now check if binary logging is working
    # cd /var/db/repl
    # ls -al

    You should see a file named binary-log.001 …. if not you have got to troubleshoot it and fix it and then delete the slave data directory and shutdown the master and copy over the data directory again before restarting. The only problem I had when I first did this was that I had a binary log name in my cnf file that mysql just did not like, so initially use “binary-log” which is sure to work.
    drwxr-xr-x 5 mysql wheel 170 24 Jun 09:32 .
    drwxr-xr-x 23 root wheel 782 24 Jun 09:32 ..
    -rw-rw---- 1 mysql wheel 20041 23 Jun 10:35 binary-log.001
    -rw-rw---- 1 mysql wheel 56 24 Jun 09:33 binary-log.index

    You can examine the file like this:
    # mysqlbinlog binary-log.001

    If you wish log into mysql and create a test database, add a table and add a record. then log out and examine the binary log and you will see the SQL commands in there ready for the slave to execute.

    If binary logging is working its time to finish with the slave

    Finish configuring the slave

    First fix privileges on the data folder that we copied over
    # cd /usr/local/mysql
    # chown -R mysql:wheel data

    Verify privileges if you wish…
    # ls -al ./data

    Now start the slave…
    # echo
    # mysqld_safe &

    Press return.

    When the slave has started, log into it and check that the test SQL stuff you did on the master has replicated. BUT DO NOT run SQL statements on the slave yourself that would jeopardize the integrity of the slave being an exact copy. If you wish create a read only user on the MASTER and then log into the slave using the read only user to verify replication.

    In addition you can go into the /usr/local/mysql/data directory and you will see the relay log. Also check out the online MASTER and SLAVE SQL commands for checking status etc.

    If it’s working then congratulations! if not …. then google it.

  • How to install MySQL 5.5 on Mac OS X 10.7 Lion

    Posted on September 8th, 2011 phpguru 8 comments

    This may not be obvious, but on the new Macs that ship with Lion, you can use the MySQL 5.5 64-bit dmg installer. It works perfectly on Lion, even though the MySQL site (still, at the time of this writing) says Mac OS X 10.6 Snow Leopard. You can use the Preference Pane to stop and start MySQL.

    Now after MySQL 5.5 is running, strangely enough, you cannot simply launch terminal and type mysql -u rootBash will complain that it can’t find mysql. So we have to help it like so:

    1. Use your favorite text editor to edit the file /Users/%yourname%/.bash_profile If this file doesn’t exist you can create it.
    2. Add the following line to your .bash_profile export PATH=$PATH:/usr/local/mysql/bin and save the file. Be careful editing this file exactly as above. You can render terminal unable to find all your programs if you break your $PATH.
    3. Quit and relaunch terminal, or type source ~/.bash_profile and hit return to reload the changes in your profile.
    4. Check your $PATH by typing echo $PATH and pressing return. You should see something like this /usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/X11/bin:/usr/local/mysql/bin
    5. Now you should be able to run mysql -u root which means there is no root password by default!
    6. Run this next, at the mysql prompt GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'your_password_here' WITH GRANT OPTION;This is how to secure your root user login.

    Now, for PHP to use this connection I had to tweak my system as shown below:

    1. Run phpinfo() and check out the path PHP is trying to use for mysql.sock. On my new Mac Mini, it was/var/mysql/mysql.sock
    2. From terminal, I did sudo find / -name mysql.sock -print
    3. The critical line of output shows that MySQL 5.5 installs the sock to /private/tmp/mysql.sock
    4. Now we need to create a symlink for PHP to be able to access the mysql.sock. Trouble is if you try it you’ll get an error because /var/mysql doesn’t exist. So next, do sudo mkdir /var/mysql
    5. Finally, do this sudo ln -s /private/tmp/mysql.sock /var/mysql/mysql.sock

    Did that work for you?

  • Apache, PHP and MySQL in 10 Minutes

    Posted on June 11th, 2011 phpguru No comments

    Whether you want to start developing web applications for the first time, you’re wanting to make better use of your local computer or a virtual machine to streamline development, create a development or staging server, or just checking back as a reference for a new platform, welcome to…

    Apache, PHP and MySQL in 10 Minutes

    In this guide you will find the resources needed to build a web development environment on the platform of your choice

    1. Choose Your Platform – Mac, Windows or Linux.
      1. Mac OS X Instructions
        1. Easy - Using MAMP
        2. Intermediate - Using built-in Apple Apache
      2. Windows Instructions
        1. Install WampServer
        2. Install XAMPP
      3. Linux Instructions
        1. Install LAMP stack on CentOS/RHEL
        2. Install LAMP stack on Debian/Ubuntu
        3. Install LAMP stack on Fedora
        4. Install LAMP stack on OpenSUSE
        5. Install LAMP stack on ArchLinux
      4. CPanel
        1. CPanel comes with Apache & PHP already installed, so here’s how to customize your install
        2. Note – make sure your config comes with WHM (Web Host Manager)
        3. Many ISPs call this a Reseller account
        4. How to use the EasyApache script
      5. Plesk
        1. Plesk comes with Apache & PHP already installed, so here’s how to customize your configuration
        2. Parallels Plesk Apache Configuration Guide
    2. Install any PHP Extensions you might need
      1. The one PHP extension that Kohana requires you that doesn’t come by default (See Kohana 3.1 in 5 minutes) is mcrypt:
      1. Pluging mcrypt into OS X
      2. Enabling mcrypt under WampServer
      3. Install mcrypt for php under Linux
      4. Install mcrypt under CPanel
    3. How to create a VirtualHost
      1. Find your Apache Config file
      2. Find your Apache VirtualHost config file
      3. Sample VirtualHost container
      4. <VirtualHost *:80>
        ServerName website.dev
        DocumentRoot "/Users/username/Sites/website.com"
        </VirtualHost>
      5. More from the Apache VirtualHost documentation
    4. How to edit your hosts file
      1. hosts file information
    5. Start Apache
      1. Start Apache
        1. Mac OS X:
          1. Terminal
            1. sudo apachectl start
          2. or start Web Sharing in System Preferences
        2. Windows
          1. WampServer – start all services using the taskbar tray icon
        3. Linux
          1. Debian/Ubuntu
            1. sudo /etc/init.d/apache2 [start | stop | restart ]
          2. Fedora
            1. service httpd [start | stop | restart]
          3. ArchLinux
            1. /etc/rc.d/httpd restart
          4. OpenSUSE
            1. apachectl [start | stop | restart]
    6. Test your install
      1. Go to http://localhost in your browser
      2. Are you seeing the Apache start page?
      3. If so, you are done. Congratulations! You just installed a web server.
    7. Install MySQL
      1. Mac OS X
        1. Installing MySQL on OS X
      2. Windows
        1. Installing MySQL on Windows
      3. Linux
        1. Installing from a binary
        2. Build MySQL 5.5 from source

     

    Now that you have a development environment, why not install a fantastic web development framework, such as Kohana.

    Ready for round two? Kohana 3.1 in 10 Minutes

  • MySQL won’t start on Snow Leopard

    Posted on December 31st, 2010 phpguru 6 comments

    I recently spent a lot of time scratching my head over this. Partial solution only at this point, any additional ideas would be most welcome.

    I restored a Time Machine backup from an older 32-bit MacBook Pro onto a new 64-bit MacBook Pro. Apache & PHP worked fine, but I had compiled the 32-bit version of 5.1 on the 32-bit box. I had problems with MySQL starting after the restore, which led me to rm -rf everything related to MySQL including the /Library/StartupItems/LaunchDaemon, reboot, and install the 5.1.54 from the 64-bit dmg download installer.

    After this, I figured I’d be fine, The MySQL preference pane in System Prefs shows “stopped”. Clicking start has no effect.

    ps aux | grep mysql

    shows only my grep command.

    I had to do a few things to get MySQL partially working.

    1) I had to

    chown -R mysql:wheel mysql-install-dir/bin
    chown -R mysql:wheel mysql-install-dir/data

    2) I discovered I can only start MySQL if, from terminal, I do:

    sudo /usr/local/mysql/bin/mysqld_safe --user=root

    I can’t connect to mysql from the command line with the dreaded

    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

    I do another

    ps aux | grep mysql

    and see it’s starting with

    /usr/local/mysql/bin/mysqld 
      --basedir=/usr/local/mysql
      --datadir=/usr/local/mysql/data
      --user=root
      --log-error=/usr/local/mysql/data/MacbookPro.local.err
      --pid-file=/usr/local/mysql/data/MacbookPro.local.pid
      --socket=/var/mysql/mysql.sock

    So I make a symlink

    ln -s /var/mysql/mysql.sock /tmp/mysql.sock

    and try again

    mysql -u root -p

    Yes! Now I can use MySQL from the command line. After Googling for hours, I’ve seen others with similar install/starting problems on the latest Snow Leopard. I think it all has to do with file permissions on the /bin and /data directories.

  • Get MySQL Certified For Just Under Four Grand

    Posted on November 15th, 2010 phpguru No comments

    Oracle just sent me an email with a “soft offer” to get 20% off MySQL Certification. I’m using GMail in Safari. I click to see the pricing. The click-through sent me to this page.

    The page requires you to pick your country, and then breaks. Second time, I get an error message: The packages couldn’t be received. Third time it worked.

    Normally, $4,635 – act fast and get MySQL Certified for only $3,708.

    So let me see if I understand this correctly… in a down economy, when the average guy is just lucky to have food on the table… I can pay just under four thousand dollars to be certified to use a free database? Hmmm. I’ve never been asked by any employer for such a certification.

    I think I’d rather eat this month.

    Great email campaign though – generated three clicks. D’OH! Fail.