Brumley Wiki MySQL/MySQL

MySQL

Stop/Start/Restart MySQL Mac

There are different cases depending on whether you installed MySQL with the official binary installer, using MacPorts, or using Homebrew:

MacPorts

sudo launchctl unload -w /Library/LaunchDaemons/org.macports.mysql.plist
sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql.plist

Note: this is persistent after reboot.

Homebrew

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

Binary installer

sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop
sudo /Library/StartupItems/MySQLCOM/MySQLCOM start
sudo /Library/StartupItems/MySQLCOM/MySQLCOM restart

There is a bug in the current (2015-01-14) version of MySQL for Mac that does not start on boot on Yosemite (maybe others). It has to do with the preference pane that is installed. If you need it to start automatically you can perform the following:

Found here: http://stackoverflow.com/questions/26461173/autostart-mysql-server-on-mac-os-x-yosemite

Reset Forgotten MySQL Root Password

Found here: http://www.howtoforge.com/reset-forgotten-mysql-root-password

Have you ever forgotten the root password on one of your MySQL servers? No? Well maybe I’m not as perfect as you. This is a quick h00tow (how to) reset your MySQL root password. It does require root access on your server. If you have forgotten that password wait for another article. Original article posted on reset mysql root password.

First things first. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.

    mysqld_safe --skip-grant-tables

You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.

    mysql --user=root mysql

    update user set Password=PASSWORD('new-password') where user='root';
    flush privileges;
    exit;

Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.

List of MySQL Commands

http://www.pantz.org/software/mysql/mysqlcommands.html

MySQL Commands:

mysqldump -

    sudo mysqldump -u root -p -l -Q --add_drop_table ogc_portal > ogc_portal_dump.sql

http://www.google.com/search?q=mysql+dump+restore

MySQL Dump/Restore

Dump ALL MySQL Databases

	mysqldump --user=XXXXXXXX --password=XXXXXXX -A > /PATH/TO/DUMPFILE.SQL

Dump Individual or Multiple MySQL Databases

	mysqldump --user=XXXXXXXX --password=XXXXXXX --databases DB_NAME1 DB_NAME2 DB_NAME3 > /PATH/TO/DUMPFILE.SQL

Dump only certain tables from a MySQL Database

	mysqldump --user=XXXXXXXX --password=XXXXXXXX --databases DB_NAME --tables TABLE_NAME > /PATH/TO/DUMPFILE.SQL

I'm using MySQL 4.1.8 on my development server, but am behind a few releases on our production server. In order to make dumps compatible with the old MySQL version, add the following switch:

	--compatible=mysql323

Use the following procedure to reload the contents of a database:

Unzip the backup file you wish to use.
Open it up and pull out only the information that you will need.
Save this text file.
Use the following command to feed back in the contents of a text file:

       mysql --verbose --user=XXXXXXXX --password=XXXXXXXX DB_NAME < /PATH/TO/DUMPFILE.SQL

http://www.patrickpatoray.com/?Page=30

MySQL ‘max_allowed_packet’ fix (a.k.a. MySQL server has gone away error)

At the shell, you can type “mysqladmin” and scroll up a bit to see what the default location options are for my.cnf.

Find the variable max_allowed_packet = 1M (the default value) and set it to 16M or however large you need it.

I use Zend Server free and running mysqladmin showed the following:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

When in reality I found it here:

/usr/local/zend/mysql/data

Another Note:

max_allowed_packet is a dynamic parameter in mysql 5.1.

You can set it when mysql server on the runtime.

using mysql to connect to the server like this:

mysql -h”remote_host” -u’user’ -p’password’.

after login in. you can using:

set global max_allowed_packet=50M

to set max_allowed_packet. No need to change my.cnf and restart mysql server.

you can refer to mysql reference at:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_allowed_packet

http://www.cmi.ac.in/~madhavan/courses/databases10/mysql-5.0-reference-manual/error-handling.html#packet-too-large