Category Archives: MySQL

Useful Apache 2 and MySQL 5 service aliases for MacPorts

Those are some aliases I use to start, stop and restart Apache 2 and MySQL on my MacPorts installation done with the installation script I made some time ago. You have to edit “~/.bash_profile” file and add those lines:

alias mysql5start='sudo /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper start'
alias mysql5stop='sudo /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper stop'
alias mysql5restart='sudo /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper restart'

alias apache2start='sudo /opt/local/etc/LaunchDaemons/org.macports.apache2/apache2.wrapper start'
alias apache2stop='sudo /opt/local/etc/LaunchDaemons/org.macports.apache2/apache2.wrapper stop'
alias apache2restart='sudo /opt/local/etc/LaunchDaemons/org.macports.apache2/apache2.wrapper restart'

Then execute “source ~/.bash_profile” to load the new aliases or just open a new terminal window.

Change root password in MySQL

In GNU/Linux:

$ sudo mysqld_safe --skip-grant-tables &
$ mysql -u root -p
#press enter to use a blank password
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit;
$ sudo service mysql stop
$ sudo service mysql start
#try your new password
$ mysql -u root -p

In Mac OS X with MySQL installed with MacPorts:

$ sudo /opt/local/lib/mysql5/bin/mysqld_safe --skip-grant-tables &
$ mysql -u root -p
#press enter to use a blank password
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit;
$ sudo port unload mysql5
$ sudo port load mysql5
#try your new password
$ mysql -u root -p

Run shell commands in Mac OS X executing a normal application

Once I installed Apache2+MySQL5+PHP5.3 using MacPorts I wanted to start and stop the services without having to open a terminal window a run the commands myself. This can be achieved creating an AppleScript that executes the command and then saving it as an application.

Open the AppleScript editor (Utilities→AppleScript Editor.app) and write the command you want to run. I had to create 4 different applications because I wanted the start and stop applications for both Apache2 and MySQL5. As the commands must be run as an administrator I added at the end “with administrator privileges” (the application will ask for the password):

do shell script "/opt/local/etc/LaunchDaemons/org.macports.apache2/apache2.wrapper start" with administrator privileges
do shell script "/opt/local/etc/LaunchDaemons/org.macports.apache2/apache2.wrapper stop" with administrator privileges
do shell script "/opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper start" with administrator privileges
do shell script "/opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper stop" with administrator privileges

It’s VERY IMPORTANT to clic the “Compile” button to be sure everything is correct and that the application is created. Now you only have to go to “File→Save…” and save it with the “Application” format.

Apache 2 + MySQL 5 + PHP 5.3 + phpMyAdmin 3.5.4 installation script for Mac OS X using MacPorts

Based on the instructions provided by Gilles Fabio I’ve created a complete installation script to install Apache 2, MySQL 5, PHP 5.3 and phpMyAdmin 3.5.4 for Mac OS X using MacPorts.

It’s important to know that as MacPorts compiles all the ports from source you have to have installed “Command Line Tools” (download it from http://connect.apple.com/, an Apple ID is needed) and once installed run xcodebuild -license from the command line to accept the EULA.

It automatically installs and configures everything you need to have a working Apache+MySQL+PHP environment. Once installed the most important files can be located here:

httpd.conf:         /opt/local/apache2/conf/httpd.conf
httpd-vhosts.conf:  /opt/local/apache2/conf/extra/httpd-vhosts.conf
htdocs folder:      /opt/local/apache2/htdocs
php.ini:            /opt/local/etc/php5/php.ini
config.inc.php:     /opt/local/apache2/htdocs/phpmyadmin/config.inc.php
my.cnf:             /opt/local/my.cnf
mysqld.sock:        /opt/local/var/run/mysql5/mysqld.sock

You can download it from my Github repository: installMacPorts-Apache-MySQL-PHP-phpMyAdmin-OSX.sh

Look at my article about how to configure the virtual hosts so you can have multiple websites in the same server.

UPDATE: You can install PHP 5.4 using this guide: https://gist.github.com/2721719

Can’t change user options in MySQL Administrator

May be you installed MySQL 5 and you are not able to change user information, schema privileges and resources for your MySQL users using the MySQL Administrator. You select “User Administration” then a “User Account” but it keeps saying “No user selected” like in this capture:

It may be caused because your mysql database doesn’t have the user_info table. Run this in MySQL Query Browser or in the MySQL terminal to create the user_info table and the problem should be fixed.

DROP TABLE IF EXISTS `mysql`.`user_info`;
CREATE TABLE  `mysql`.`user_info` (
  `User` varchar(16) COLLATE utf8_bin NOT NULL,
  `Full_name` varchar(60) COLLATE utf8_bin DEFAULT NULL,
  `Description` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `Email` varchar(80) COLLATE utf8_bin DEFAULT NULL,
  `Contact_information` text COLLATE utf8_bin,
  `Icon` blob,
  PRIMARY KEY (`User`),
  KEY `user_info_Full_name` (`Full_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Stores additional user information';

People upgrading to MySQL 5 from other versions have solved this problem by executing the following command inside bin folder of MySQL installation directory:

mysqlcheck --check-upgrade --all-databases --auto-repair

And then running the mysql_fix_privilege_tables.sql script. You have to login to MySQL as root on the mysql database running this command from the bin folder of MySQL installation directory:

mysql -u root -p mysql

Type root’s password when asked and then run this in the MySQL terminal:

SOURCE ../scripts/mysql_fix_privilege_tables.sql;

If you don’t have the mysql_fix_privilege_tables.sql file in the scripts folder (or you don’t have that folder at all), download a zip archive noinstall version of MySQL, extract it and search that file. Or you can just download the file from here: mysql_fix_privilege_tables.sql.zip. Don’t forget unzipping it and copying it to your scripts folder inside the MySQL installation directory.

Once the script is completed restart your MySQL server.

Source: http://bugs.mysql.com/bug.php?id=25515