Tag Archives: mysql

Migrate from mysql to percona

1
2
gpg --keyserver hkp://keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
gpg -a --export CD2EFD2A | sudo apt-key add -

add to /etc/apt/sources.list.d/percona.list

1
2
deb http://repo.percona.com/apt VERSION main
deb-src http://repo.percona.com/apt VERSION main

where VERSION is a name of your ubuntu version,
you can get it by command

1
lsb_release -a |grep name |awk '{print $2}'

backup data

1
2
cp -Rf /var/lib/mysql /var/lib/mysql-old
cp /etc/my.cnf /etc/my.cnf-old

Install percona (it automatically removes mysql and converts databases)

1
2
apt-get update
apt-get install percona-server-server-5.5 percona-server-client-5.5

Part of this article was taken from http://planet.jboss.org/post/drop_in_migration_from_mysql_to_percona_db_in_two_minutes

MySQL: csv output

$q = «select ...where something IN ('...','...','...')»

mysql database -B -h192.168.198.67 -u username -ppassword -e «START TRANSACTION WITH CONSISTENT SNAPSHOT; $q;COMMIT;»

1
| sed 's/\t/,/g'

>> output_file.csv

Rails: dump db schema from mysql

gem install mysql2

edit config/database.yml

development:
    adapter: mysql2
    encoding: utf8
    database: db_name
    username: user_name
    password: password_of_user
    host: host_ip
    timeout: 5000

edit Gemfile

gem 'mysql2'

bundle install

rake db:schema:dump

db/schema.rb has been created

MySQL: Unknown table engine ‘InnoDB’

mysql> show innodb status
Unknown table engine ‘InnoDB’

I got this message when tried to allocate a huge size of memory on remote server,
innodb_buffer_pool_size = 8000M

after i have looked for memory:
free -m
and have found that there are only 4gb,

change option to:
innodb_buffer_pool_size = 2000M

and mysql has been start fine with innodb.

MySQL: change data folder in ubuntu

sudo su
mkdir -p /data/mysql
mv /var/lib/mysql/* /data/mysql
rm -rf /var/lib/mysql
ln -s /data/mysql /var/lib/mysql
chown -R mysql.mysql /data/mysql /var/lib/mysql

edit /etc/mysql/conf.d/datadir.cnf // create this file
[mysqld]
datadir=                /data/mysql

edit /etc/apparmor.d/usr.sbin.mysqld
after
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,

add
/data/mysql/ r,
/data/mysql/** rwk,

/etc/init.d/apparmor reload
/etc/init.d/mysql restart

MySQL: upgrade from 5.0 to 5.1

1) stop all applications which works with mysql
2) make mysqldump(with right options like --single-transaction and --master-data)
3) make logs flush; in mysql
4) stop mysql
5) backup /var/lib/mysql folder (it's default path to mysql folder)
6) install new version of mysql (in ubuntu: aptitude install mysql-server-5.1
7) execute mysql_upgrade command in console
8) start mysql

MySQL: Transactions in MyISAM and InnoDB

Test

CREATE TABLE `t01` (
`column_of_t01` int(11) DEFAULT NULL
) ENGINE=MyISAM;

CREATE TABLE `t02` (
`column_of_t02` int(11) DEFAULT NULL
) ENGINE=InnoDB;
start transaction;
insert into t01(column_of_t01) values(666);
insert into t02(column_of_t02) values(666);
select * from t01;
select * from t02;
rollback;
select * from t01;
select * from t02;

Continue reading MySQL: Transactions in MyISAM and InnoDB