Automate MySQL login

When scripting MySQL from bash or when frequently accessing mysql command prompt it is useful not to have to enter the login credentials all the time. This however can be automated so you don’t have to do it.

In your home folder create .my.cnf file with the following content:

[client]
username=mysql_username
password=myslq_password

and that’s it. Now to access MySQL console just type in: myslq

So for example to see databases from bash we just run:

~$ echo show databases | mysql
Database
information_schema
dbCustomerInfo
mydb1
mydb2
mydb3
mysql
performance_schema
store

Convert all MySQL tables to MyISAM from InnoDB

Run this SQL statement (in the mysql client, phpMyAdmin, or wherever) to retrieve all the InnoDB tables in your database.

Replace value of the name_of_your_db variable with your database name.

SET @DATABASE_NAME = 'name_of_your_db';
SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=MyISAM;') AS sql_statements
 FROM information_schema.tables AS tb
 WHERE table_schema = @DATABASE_NAME
 AND `ENGINE` = 'InnoDB'
 AND `TABLE_TYPE` = 'BASE TABLE'
 ORDER BY table_name DESC;

Then, copy the output and run as a new SQL query.

Pi vs UDOO MySQL performance test

Create test DB:

mysql -u root -p
mysql> create database test;
exit;

Prepare the database:

sysbench --test=oltp --oltp-table-size=100000 --mysql-db=test --mysql-user=root --mysql-password=mysqlpassword prepare

Run the test:

sysbench --test=oltp --oltp-table-size=100000 --mysql-db=test --mysql-user=root --mysql-password=mysqlpassword --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run

Pi Output:

OLTP test statistics:
    queries performed:
        read:                            28364
        write:                           0
        other:                           4052
        total:                           32416
    transactions:                        2026   (33.76 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 28364  (472.67 per sec.)
    other operations:                    4052   (67.52 per sec.)

Test execution summary:
    total time:                          60.0080s
    total number of events:              2026
    total time taken by event execution: 59.8767
    per-request statistics:
         min:                                 12.90ms
         avg:                                 29.55ms
         max:                                 76.67ms
         approx.  95 percentile:              50.34ms

Threads fairness:
    events (avg/stddev):           2026.0000/0.00
    execution time (avg/stddev):   59.8767/0.00

UDOO output:

OLTP test statistics:
    queries performed:
        read:                            287532
        write:                           0
        other:                           41076
        total:                           328608
    transactions:                        20538  (342.19 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 287532 (4790.71 per sec.)
    other operations:                    41076  (684.39 per sec.)

Test execution summary:
    total time:                          60.0186s
    total number of events:              20538
    total time taken by event execution: 479.3667
    per-request statistics:
         min:                                  7.26ms
         avg:                                 23.34ms
         max:                                196.47ms
         approx.  95 percentile:              32.59ms

Threads fairness:
    events (avg/stddev):           2567.2500/50.73
    execution time (avg/stddev):   59.9208/0.01

the important bit is the transactions line:
UDOO: transactions: 20538 (342.19 per sec.)
PI: transactions: 2026 (33.76 per sec.)

Last step – drop the test database:

mysql -u root -p
mysql> drop database test;
exit;

 

MySQL replication

I have 2 servers, one of which is a Master (my UDOO box) and the other which is a Slave (Raspberry PI). We tell the Master that it should keep a log of every action performed on it. We tell the slave server that it should look at this log on the Master and whenever something new happens, it should do the same thing.

Step 1. Create sleve_replication_user_account:

 

GRANT REPLICATION SLAVE ON *.* TO 'sleve_replication_user_account'@'%' 
  IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;

 

Step 2. Setup your Master.

Login to your “master” mysql server phpmyadmin and go to the replication tab, under Master configuration select set the options as your require:mysql1

Then copy the generated by PhpMyAdmin config and paste it to your my.cnf config file and restart your mysql server.

Go back to your phpmyadmin installation to verify that everything is working OK:

mysql2

Step 3. Setup your Slave.

Open Phpmyadmin on your slave go to replication tab and fill in the bits:

mysql3

Don’t forget to add the server-id to the slave my.cnf config.

Then add list of databases you want to replicate:

replicate-do-db=database_name1
replicate-do-db=database_name2
.....

This should match the config on your Master. Save your config and restart the mysql service.

Step 4. Export your DBs on Master to sql file

Run:

FLUSH TABLES WITH READ LOCK;

Then use Phpmyadmin to export one by one all the databases that you want to replicate.

Step 5. Import your DBs on Slave from exported sql files

Transfer exported sql files from Master to Slave.

log into MySQL, and create the databases:

mysql -u root -p
CREATE DATABASE `database_name1`;

then import the data:

mysql -u root -p database_name1  </home/my_home_dir/database_name1.sql

 

Step 6. Get it all running.

Now we are ready to kick things off. We need to find the position the Master is at in the logs. So, log into MySQL on your Master and run the following:

 mysql -u root -p
SHOW MASTER STATUS;

result:

+---------------------+----------+----------------------+------------------+
| File                | Position | Binlog_Do_DB         | Binlog_Ignore_DB |
+---------------------+----------+----------------------+------------------+
| mysql-bin.000076    | 00027442 | database_name1;      |                  |
+---------------------+----------+----------------------+------------------+

make note of this and login to MySql on your Slave:

mysql -u root -p
slave stop;
 CHANGE MASTER TO MASTER_HOST='AA.BB.CC.DD', MASTER_USER='slave_user', 
    MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000076', 
    MASTER_LOG_POS=00027442;
slave start;

where AA.BB.CC.DD is your Master IP address

then go back to the Master and run:

unlock tables;

And that is pretty much it.

 

Troubleshooting.

If the servers get out of sync run this on the Slave:

STOP SLAVE;
 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000049';
 CHANGE MASTER TO MASTER_LOG_POS=98;
 START SLAVE;

Note: MASTER_LOG_FILE must be the last file where it stop from replicating

Install LAMP Server in Ubuntu Server 12.04 LTS

Install LAMP Stack (Linux, Apache, Mysql, PHP) with command below:

sudo apt-get install lamp-server^

Install PHP5 (Some applications require other PHP5 mods) with command:

sudo apt-get install php5 php5-gd php5-mysql php5-curl php5-cli php5-cgi php5-dev

Install phpmyadmin

sudo apt-get install phpmyadmin