Currently Browsing: MySql

Rackspace API: Trove install to enable root user for cloud DB

Rackspace Trove set-up:

Mac OSX:

sudo easy_install pip
sudo pip install python-troveclient

# trove:
# http://docs.rackspace.com/cdb/api/v1.0/cdb-getting-started/content/Install_Trove_Client.html

trove list
trove root-enable

Post-Mac Mavericks install tasks

stuff I needed to do in order to restore some functionality once updating to Mavericks.

(Uses native apache/php with homebrew supplements, no MAMP or similar packages required.)

here we go:


* update .bash_profile in ~/
# Set architecture flags
export ARCHFLAGS="-arch x86_64"
# Ensure user-installed binaries take precedence
export PATH=/usr/local/bin:$PATH
# Load .bashrc if it exists
test -f ~/.bashrc && source ~/.bashrc

$ xcode-select --install

* You will then be asked whether you want to install Xcode or 
    the command line developer tools, with the latter being the default.

* install Homebrew:
$ ruby -e "$(curl -fsSL https://raw.github.com/mxcl/homebrew/go/install)"

* install some utils:
$ brew install bash-completion ssh-copy-id wget

* install mysq
$ brew install mysql --enable-local-infile

# To have launchd start mysql at login:
#    $ ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents
# Then to load mysql now:
#   $ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
# Or, if you don't want/need launchctl, you can just run:
#   $ mysql.server start
#
#   $ mysql_secure_installation

$ sudo mkdir /var/mysql
$ sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

* php myadmin:
$ brew tap homebrew/dupes
$ brew tap josegonzalez/homebrew-php
$ brew install phpmyadmin

$ sudo cp /usr/local/share/phpmyadmin/config.sample.inc.php \ 
    /usr/local/share/phpmyadmin/config.inc.php

* PHP
#  no problems as of yet. was able to compile extensions with brew and link those
#  to the native Apache/PHP setup via php.ini
$ sudo cp /etc/php.ini.default /etc/php.ini
    # and merge previous php.ini
# Zend Opcache seems to be more stable than APC:
$ brew install php54-opcache
    # copy/link .so file and update php.ini

* redis:
$ brew install redis
* phpredis:
$ brew install php54-redis
    # copy/link .so file and update php.ini

* Apache
#  was generally fine, might need to change some perms, particularly if
#  putting web directories under your home folder, e.g., Dropbox.
#  this fixed that: (you could update your groups theoretically to avoid
#  setting the search (+x) permission for "other".
$ sudo chmod -R g+x Dropbox/
$ sudo chmod -R o+x Dropbox/

MySql replication re-sync for master-slave

If your master-slave DBs get out of sync, you can try to reset the slave with:

stop slave
reset slave
slave start

use show slave status to check for Slave_IO_Running and Slave_SQL_Running

If this doesn’t work, you may have to re-import the data from Master to Slave via:

Master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

And copy the values of the result of the last command somewhere.

Without closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

$ mysqldump -uroot -p --all-database > /a/path/mysqldump.sql

Now you can release the lock, even if the dump hasn’t end. To do it perform the following command in the mysql client:

UNLOCK TABLES;

Now copy the dump file to the slave using scp or your preferred tool.

Slave:

Open a connection to mysql and type:

STOP SLAVE;

Load master’s data dump with this console command:

$ mysql -uroot -p < mysqldump.sql

Sync slave and master logs:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

Where the values of the above fields are the ones you copied before.

Finally type

START SLAVE;

And to check that everything is working again, if you type

SHOW SLAVE STATUS;

you should see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

thanks to SO

Change character set and collation for all columns MySql

ALTER TABLE tiles CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

Multiple databases in Yii: MySql + SQLite

If you need two (or more) completely different db connections in the same app (e.g, MySql + SQLite), using this method works better than trying to dynamically change the static variable CActiveRecord::db. (If you are using multiple MySql dbs you could get away with a more simple solution like that, which could even take advantage of cross-db queries, which this method won’t.)

In this example, we want to read/write press release data to SQLite for the Press model:

  • main config file:
    'components'=>array(
      ....
    	'dbpress' => array(
    		'connectionString'	=> 'sqlite:' . '/path/to/press.db',
    		'charset' => 'utf8',
    		'class' => 'CDbConnection'
    	),
  • Press model:
    class Press extends CActiveRecord
    {
    // second db support:
        private static $dbpress = null;
     
        protected static function getPressDbConnection()
        {
            if (self::$dbpress !== null)
                return self::$dbpress;
            else {
                self::$dbpress = Yii::app()->dbpress;
                    self::$dbpress->setActive(true);
                    return self::$dbpress;
            }
        }
    
        public function getDbConnection()
        {
            return self::getPressDbConnection();
        }
    //
  • that’s it!

    « Previous Entries Next Entries »