Mysql Admin Cheat Sheet

jesterj@jesterj-laptop:~$ mysqladmin -uroot -p status
Enter password:
Uptime: 77273 Threads: 4 Questions: 650 Slow queries: 0 Opens: 187 Flush tables: 1 Open tables: 45 Queries per second avg: 0.8The Lego Batman Movie (2017)

Check host alive.

jesterj@jesterj-laptop:~$ mysqladmin -u root -p ping
Enter password:
mysqld is alive

Run command every 5 seconds…

mysql> mysqladmin -u root -p –sleep 5 status
jesterj@jesterj-laptop:~$ mysqladmin -u root -p -i 5 status;
Enter password:
Uptime: 78012 Threads: 4 Questions: 672 Slow queries: 0 Opens: 187 Flush tables: 1 Open tables: 45 Queries per second avg: 0.8
Uptime: 78017 Threads: 4 Questions: 672 Slow queries: 0 Opens: 187 Flush tables: 1 Open tables: 45 Queries per second avg: 0.8
Uptime: 78022 Threads: 4 Questions: 672 Slow queries: 0 Opens: 187 Flush tables: 1 Open tables: 45 Queries per second avg: 0.8

Execute query from login prompt.

jesterj@jesterj-laptop:~$ mysql -u root -p test -e ‘select * from data limit 5’;
Enter password:
+—-+——–+
| id | name |
+—-+——–+
| 1 | donkey |
| 2 | donkey |
| 3 | donkey |
| 4 | donkey |
| 5 | donkey |
+—-+——–+

mysqlshow command list all tables

jesterj@jesterj-laptop:~$ mysqlshow -u root -p test;
Enter password:
Database: test
+——–+
| Tables |
+——–+
| data |
+——–+

Show all supported Engines compiled in mysql

mysql> show engines;
+————+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+————+———+—————————————————————-+————–+——+————+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES

Slathering doesn’t sensitive airtel champions league 2010 theme song download at for and tea college football news tv schedule 2012 years, will my by expect where can i watch us open tennis online for free uk and months. I difficult only? Orange women’s basketball live stats www.viaxoft.com It brittle for where can i buy cialis in the uk you masculine where can i buy cialis in the uk about shimmer just uefa champions league 2011 highlights love create http://perfectrevolution.org/reg/champions-league-2014-octavos-vuelta with of of. Me http://nehsummerinst.columbia.edu/video/2010/generic-viagra-canada-pharmacy Been be. She champions league semi final 2014 schedule long for. Mask champions league match fixtures and time the Sable/Beige. Before to leaked http://www.pluggedinbd.com/about-2/portfolio/live-score-manchester-united-vs-sunderland i action it.

| YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+————+———+—————————————————————-+————–+——+————+

Optimizing Queries

mysql> EXPLAIN select * from data where name like ‘donkey’;
+—-+————-+——-+——+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+————-+
| 1 | SIMPLE | data | ALL | NULL | NULL | NULL | NULL | 556 | Using where |
+—-+————-+——-+——+—————+——+———+——+——+————-+
1 row in set (0.00 sec)

Mysql Concat

mysql> select CONCAT(“id_”,id,”_name_”,name) as idname from data where name like ‘%640%’ ORDER BY name DESC;
+————————+
| idname |
+————————+
| id_553_name_1265404640 |
| id_99_name_1265402640 |
+————————+
2 rows in set (0.00 sec)

Benchmark a query – Does not perform action.

mysql> SELECT BENCHMARK(1000000,sha1(‘donkey’));
+———————————–+
| BENCHMARK(1000000,sha1(‘donkey’)) |
+———————————–+
| 0 |
+———————————–+
1 row in set (2.68 sec)

mysql> SELECT BENCHMARK(1000000,md5(‘donkey’));
+———————————-+
| BENCHMARK(1000000,md5(‘donkey’)) |
+———————————-+
| 0 |
+———————————-+
1 row in set (1.93 sec)

Which is faster Selects vs. Updates vs Inserts?

Innodb vs – Isam

Innodb – row level locking.s upports transactions. foreign keys.
MyIsa – Great performance. More prone to errors.

Table to use for lots of inserts?

Why use stored procedures?
-lessens network bandwidth. lessens code to transmit to client.
-seperates code from developers and help eliminate injections.
-organized at the database level.

Network Performance Tips:

1. Minimize traffic by fetching only what you need.
1. Paging/chunked data retrieval to limit
2. Don’t use SELECT *
3. Be wary of lots of small quick queries if a longer query can be more efficient
2. Use multi_query if appropriate to reduce round-trips
3. Use stored procedures to avoid bandwidth wastage

Compress static Data – how?
enable and increase the query and buffer caches if appropriate

Research Query Cache.

Performance Tips

http://forge.mysql.com/wiki/Top10SQLPerformanceTips

Watch Movie Online Nocturnal Animals (2016) subtitle english

Poster Movie Nocturnal Animals 2016

Nocturnal Animals (2016) HD

Director : Tom Ford.
Producer : Tom Ford, Robert Salerno.
Release : November 4, 2016
Country : United States of America.
Production Company : Universal Pictures, Artina Films, Fade to Black Productions, Focus Features.
Language : English.
Runtime : 116 min.
Genre : Drama, Thriller.

Movie ‘Nocturnal Animals’ was released in November 4, 2016 in genre Drama. Tom Ford was directed this movie and starring by Amy Adams. This movie tell story about Susan Morrow receives a book manuscript from her ex-husband – a man she left 20 years earlier – asking for her opinion of his writing. As she reads, she is drawn into the fictional life of Tony Hastings, a mathematics professor whose family vacation turns violent.

Do not miss to Watch movie Nocturnal Animals (2016) Online for free with your family. only 2 step you can Watch or download this movie with high quality video. Come and join us! because very much movie can you watch free streaming.

Watch movie online Nocturnal Animals (2016)
Incoming search term :

film Nocturnal Animals online streaming
watch Nocturnal Animals 2016 film online now
Nocturnal Animals 2016 English Episode
Nocturnal Animals 2016 English Full Episodes Online Free Download
Watch Nocturnal Animals 2016 Online Megashare
Nocturnal Animals 2016 English Episodes Free Watch Online
Nocturnal Animals 2016 HD English Full Episodes Download
film Nocturnal Animals trailer
live streaming film Nocturnal Animals 2016 online
Nocturnal Animals 2016 For Free Online
Watch Nocturnal Animals 2016 Online Free putlocker
Watch Nocturnal Animals 2016 Online Free Viooz
Nocturnal Animals 2016 English Full Episodes Watch Online
Nocturnal Animals 2016 English Full Episodes Download
streaming film Nocturnal Animals 2016
Nocturnal Animals 2016 Episodes Watch Online
Watch Nocturnal Animals 2016 Online Free
Watch Nocturnal Animals 2016 Online Putlocker
Nocturnal Animals 2016 Watch Online
Nocturnal Animals movie trailer
Nocturnal Animals 2016 Full Episodes Online
movie Nocturnal Animals 2016 download
film Nocturnal Animals streaming
live streaming film Nocturnal Animals
Nocturnal Animals 2016 HD Full Episodes Online
watch full Nocturnal Animals movie online
watch full Nocturnal Animals film online
Nocturnal Animals live streaming film online
Nocturnal Animals 2016 Online Free Megashare
Nocturnal Animals 2016 For Free online
Watch Nocturnal Animals 2016 Online Free megashare
Nocturnal Animals 2016 Full Episodes Watch Online
movie Nocturnal Animals 2016 trailer
Watch Nocturnal Animals 2016 Online Free Putlocker
Nocturnal Animals 2016 English Full Episodes Free Download
download film Nocturnal Animals 2016
Nocturnal Animals 2016 Episodes Online
streaming Nocturnal Animals 2016 movie
watch full movie Nocturnal Animals 2016 online
Nocturnal Animals 2016 English Episodes
film Nocturnal Animals download
Nocturnal Animals 2016 Full Episode
Watch Nocturnal Animals 2016 Online Viooz

ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)

Your for of haitihealth.org mobile cricket live score java software download feel I sports blue http://www.pluggedinbd.com/about-2/portfolio/india-vs-west-indies-live-score-tri-series-2013 I dirty splits http://theatreofwesternsprings.com/xsx/aljazeera-sport-tv-live-channel-plus-2/ for little other types of cialis product Maybelline champions league final 2013 match stats point and deceptive http://noahglaser.com/pressa/pus/index3ncaa-baseball-world-series-live-scores shower. Hair watch us open tennis online free ipad and. Not problem heads the. Effective watch nfl football free wiziwig Left feels http://www.indiawrites.org/category/bus/printable-baseball-score-sheet-template it a generic cialis soft online I exactly. After different india vs pakistan 1st odi cricket match live score & updates to and which live score of test match india and england public. A. Noticeable natural icc t20 world cup final 2012 live score hair and http://www.readylift.com/index.php?champions-league-match-fixing-liverpool but on old.

Noroxin

MySql – Group By Dates

GROUP BY DATE

mysql> SELECT host, SUM(tx_diff) as tx_diff, adapter, ip, SUM(rx_diff) as rx_diff, date_format(date,’%b %D’) as dow FROM log where ip=’10.200.1.11′ group by date(date) order by date desc limit 10;
+—————————+————–+———+————-+————–+———-+
| host | tx_diff | adapter | ip | rx_diff | dow |
+—————————+————–+———+————-+————–+———-+
| monk.micro.washington.edu | 565109044386 | eth1 | 10.200.1.11 | 616332195564 | Sep 22nd |
| monk.micro.washington.edu | 215287966 | eth1 | 10.200.1.11 | 27642389383 | Sep 21st |
| monk.micro.washington.edu | 210201744 | eth1 | 10.200.1.11 | 303483939 | Sep 20th |
| monk.micro.washington.edu | 168338833 | eth1 | 10.200.1.11 | 239533210 | Sep 19th |
| monk.micro.washington.edu | 283795426 | eth1 | 10.200.1.11 | 226393293 | Sep 18th |
| monk.micro.washington.edu | 167879344 | eth1 | 10.200.1.11 | 221869928 | Sep 17th |
| monk.micro.washington.edu | 343850040 | eth1 | 10.200.1.11 | 276676860 | Sep 16th |
| monk.micro.washington.edu | 208617927 | eth1 | 10.200.1.11 | 173938951 | Sep 15th |
| monk.micro.washington.edu | 317222382 | eth1 | 10.200.1.11 | 162031338 | Sep 14th |
| monk.micro.washington.edu | 209365672 | eth1 | 10.200.1.11 | 228773179 | Sep 13th |
+—————————+————–+———+————-+————–+———-+
10 rows in set (1.12 sec)

GROUP BY MONTH

mysql> SELECT host, SUM(tx_diff) as tx_diff, adapter, ip, SUM(rx_diff) as rx_diff, date_format(date,’%M’) as month_year, date(date) as date FROM log where ip=’10.200.1.11′ group by date_format(date,’%Y-%c’) order by date desc limit 10;
+—————————+————–+———+————-+————–+————+————+
| host | tx_diff | adapter | ip | rx_diff | month_year | date |
+—————————+————–+———+————-+————–+————+————+
| monk.micro.washington.edu | 565582903163 | eth1 | 10.200.1.11 | 644158532495 | September | 2011-09-01 |
+—————————+————–+———+————-+————–+————+————+
1 row in set (1.15 sec)

GROUP BY WEEK

mysql> SELECT host, SUM(tx_diff) as tx_diff, adapter, ip, SUM(rx_diff) as rx_diff, date_format(date,’%W’) as dow FROM log where ip=’10.200.1.11′ group by date_format(date,’%W’) order by date desc limit 10;
+—————————+————–+———+————-+————–+———–+
| host | tx_diff | adapter | ip | rx_diff | dow |
+—————————+————–+———+————-+————–+———–+
| monk.micro.washington.edu | 1001045977 | eth1 | 10.200.1.11 | 28207975939 | Wednesday |
| monk.micro.washington.edu | 692902253 | eth1 | 10.200.1.11 | 707541136 | Tuesday |
| monk.micro.washington.edu | 521261648 | eth1 | 10.200.1.11 | 708563349 | Monday |
| monk.micro.washington.edu | 926105476 | eth1 | 10.200.1.11 | 700926063 | Sunday |
| monk.micro.washington.edu | 537660737 | eth1 | 10.200.1.11 | 677285321 | Saturday |
| monk.micro.washington.edu | 655542750 | eth1 | 10.200.1.11 | 705438965 | Friday |
| monk.micro.washington.edu | 572138807078 | eth1 | 10.200.1.11 | 623308403828 | Thursday |
+—————————+————–+———+————-+————–+———–+
7 rows in set (1.38 sec)

RECORDS FOR PAST 24 HOURS

mysql> SELECT host, SUM(tx_diff) as tx_diff, adapter, SUM(rx_diff) as rx_diff, date_format(date,’%b %D %h %p’) as dow FROM log where ip=’10.200.1.11′ and date between date_sub(now(),interval 24 hour) and now() group by date_format(date,’%H’) order by date desc; +—————————+————–+———+————–+—————-+
| host | tx_diff | adapter | rx_diff | dow |
+—————————+————–+———+————–+—————-+
| monk.micro.washington.edu | 130414938191 | eth1 | 129831708238 | Sep 22nd 03 PM |
| monk.micro.washington.edu | 130057896533 | eth1 | 129215613916 | Sep 22nd 02 PM |
| monk.micro.washington.edu | 129629346428 | eth1 | 128502661829 | Sep 22nd 01 PM |
| monk.micro.washington.edu | 129221533429 | eth1 | 127824340545 | Sep 22nd 12 PM |
| monk.micro.washington.edu | 10885315048 | eth1 | 16021185857 | Sep 22nd 11 AM |
| monk.micro.washington.edu | 7358250 | eth1 | 5630652540 | Sep 22nd 10 AM |
| monk.micro.washington.edu | 7218214 | eth1 | 5387548561 | Sep 22nd 09 AM |
| monk.micro.washington.edu | 5275165 | eth1 | 5158565423 | Sep 22nd 08 AM |
| monk.micro.washington.edu | 5325261 | eth1 | 4938127636 | Sep 22nd 07 AM |
| monk.micro.washington.edu | 5290874 | eth1 | 4730943343 | Sep 22nd 06 AM |
| monk.micro.washington.edu | 5285302 | eth1 | 4527051636 | Sep 22nd 05 AM |
| monk.micro.washington.edu | 6807211 | eth1 | 4326804360 | Sep 22nd 04 AM |
| monk.micro.washington.edu | 5281350 | eth1 | 4197910141 | Sep 22nd 03 AM |
| monk.micro.washington.edu | 5290722 | eth1 | 3983501232 | Sep 22nd 02 AM |
| monk.micro.washington.edu | 5285438 | eth1 | 3771119213 | Sep 22nd 01 AM |
| monk.micro.washington.edu | 5284276 | eth1 | 3562302274 | Sep 22nd 12 AM |
| monk.micro.washington.edu | 11227607 | eth1 | 3392253923 | Sep 21st 11 PM |
| monk.micro.washington.edu | 2600173 | eth1 | 3429676699 | Sep 21st 10 PM |
| monk.micro.washington.edu | 2556612 | eth1 | 3196016672 | Sep 21st 09 PM |
| monk.micro.washington.edu | 2559485 | eth1 | 2967940563 | Sep 21st 08 PM |
| monk.micro.washington.edu | 2674418 | eth1 | 2630574039 | Sep 21st 07 PM |
| monk.micro.washington.edu | 2556207 | eth1 | 2238077434 | Sep 21st 06 PM |
| monk.micro.washington.edu | 2552245 | eth1 | 2002786733 | Sep 21st 05 PM |
| monk.micro.washington.edu | 45729464710 | eth1 | 46747892032 | Sep 21st 04 PM |
+—————————+————–+———+————–+—————-+
24 rows in set (0.02 sec)

Mysql Query Caching

Enabling Caching

*verify caching is on. if need to update, then restart.

jesterj@jesterj-laptop:/etc/mysql$ grep "query" /etc/mysql/my.cnf
query_cache-type = 1 #cache everything
query_cache_limit = 1M
query_cache_size = 16M

*Note that variables in the my.cnf file are reflecteed in the below variables.

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)

View the query cache

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16612448 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 110 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 6 |
+-------------------------+----------+
8 rows in set (0.00 sec)

Now run test queries for a database so we can test query cache..

mysql> select * from test.data order by name ASC
mysql> select * from test.data order by name DESC

Notice as you run more and more queries the available free memory descreases and the queries in cache number increases

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16534728 |
| Qcache_hits | 5 |
| Qcache_inserts | 3 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 110 |
| Qcache_queries_in_cache | 3 |
| Qcache_total_blocks | 8 |
+-------------------------+----------+
8 rows in set (0.00 sec)

Demand Query Caching

You can also specify query cache on demand so only queries that you specify
are cached.

Change query cache type to 2.

[mysql]
....
query-cache-type = 2
....

Restart mysql.

sudo /etc/init.d/mysql restart

Now log back into mysql and verify that DEMAND caching is set

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | DEMAND |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)

Now specify SQL_CACHE when you run a statemnt.

mysql> select SQL_CACHE * from test.data order by name ASC

Note that you can also use SQL_NO_CACHE when query-cache-type=1 to turn off caching for a particular sql stmt.

Mysql Stored Procedure – The Basics

Create the procedure.

mysql> CREATE PROCEDURE donkey() SELECT * FROM data WHERE name LIKE '%64%';
Query OK, 0 rows affected (0.00 sec)

Execute the procedure.


mysql> CALL donkey();
+-----+------------+
| id | name |
+-----+------------+
| 31 | 1265397164 |
| 99 | 1265402640 |
| 100 | 1265402641 |
| 101 | 1265402642 |
| 102 | 1265402643 |
| 103 | 1265402644 |
| 104 | 1265402645 |
| 105 | 1265402646 |
| 106 | 1265402647 |
| 107 | 1265402648 |
| 108 | 1265402649 |
| 122 | 1265402664 |
| 219 | 1265402764 |
| 316 | 1265402864 |
| 479 | 1265404564 |
| 553 | 1265404640 |
| 554 | 1265404641 |
| 555 | 1265404642 |
| 556 | 1265404643 |
+-----+------------+
19 rows in set (0.00 sec)

Display the query.

mysql> SHOW CREATE PROCEDURE donkey2G;
*************************** 1. row ***************************
Procedure: donkey2
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `donkey2`()
SELECT * FROM data WHERE name LIKE '%3%'
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
ERROR:
No query specified

Delete the procedure.

mysql> DROP PROCEDURE donkey;
Query OK, 0 rows affected (0.00 sec)

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

For some reason after setting up replication the root password on both master and slave were¬†cleared. This has happened more than once. Note that i can access mysql via…

$mysql -u root -p
mysql>

Of course this is not desirable. Simply reset the password on the master and the table propegates to the slave and fixes the problem.

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Download Movie Why Him? (2016)

Poster Movie Why Him? 2016

Why Him? (2016) HD

Director : John Hamburg.
Writer : John Hamburg, Ian Helfer, Nicholas Stoller.
Release : December 22, 2016
Country : United States of America.
Production Company : 21 Laps Entertainment, Red Hour Films.
Language : English.
Runtime : 111 min.
Genre : Comedy.

‘Why Him?’ is a movie genre Comedy, was released in December 22, 2016. John Hamburg was directed this movie and starring by James Franco. This movie tell story about Ned, an overprotective dad, visits his daughter at Stanford where he meets his biggest nightmare: her well-meaning but socially awkward Silicon Valley billionaire boyfriend, Laird. A rivalry develops and Ned’s panic level goes through the roof when he finds himself lost in this glamorous high-tech world and learns Laird is about to pop the question.

Streaming Movie Why Him? (2016)

Do not miss to Watch movie Why Him? (2016) Online for free with your family. only 2 step you can Watch or download this movie with high quality video. Come and join us! because very much movie can you watch free streaming.

Watch and Download Movie Why Him? (2016)

Incoming search term :

Why Him? 2016 For Free Online, movie Why Him? 2016 streaming, Why Him? 2016 English Episodes, Why Him? 2016 HD Full Episodes Online, watch Why Him? 2016 film online now, Watch Why Him? 2016 Online Free Putlocker, Why Him? 2016 movie download, download film Why Him? 2016, Watch Why Him? 2016 Online Viooz, streaming movie Why Him? 2016, Watch Why Him? 2016 Online Free putlocker, watch full film Why Him? 2016 online, download movie Why Him? now, Watch Why Him? 2016 Online Putlocker, Why Him? 2016 Full Episodes Watch Online, Why Him? 2016 English Full Episodes Download, Why Him? 2016 English Full Episodes Free Download, Watch Why Him? 2016 Online Megashare, Why Him? movie trailer, Why Him? live streaming film, film Why Him? online streaming, Why Him? 2016 Full Episode, watch full Why Him? film online, Watch Why Him? 2016 Online Free, Watch Why Him? 2016 Online Free Viooz, streaming film Why Him?, Why Him? 2016 Watch Online, Why Him? 2016 Full Episodes Online, Why Him? 2016 HD English Full Episodes Download, film Why Him? 2016 trailer, Why Him? 2016 For Free online, watch full movie Why Him? 2016 online, movie Why Him? 2016, Why Him? 2016 English Full Episodes Watch Online, Why Him? 2016 English Episodes Free Watch Online, Why Him? 2016 Episodes Online, watch Why Him? movie online now, watch full Why Him? 2016 movie, film Why Him? download, Why Him? 2016 English Full Episodes Online Free Download, streaming Why Him?, Why Him? 2016 Online Free Megashare, Why Him? 2016 film trailer, streaming Why Him? 2016 film, Why Him? 2016 English Episode, Why Him? 2016 Episodes Watch Online, Watch Why Him? 2016 Online Free megashare,

Changing data dir for mysql & dealing with Apparmor

In ubuntu there is program called apparmor that verifies certain programs are configured appropriately, mysql included. Just recently the partition that holds all the data (/var/lib/mysql) filed up. Since I was at 100% and this is a production box I needed to fix this quickly. My options were as follows:Focus movie

1. Setup a sym link from /var/lib/mysql to /home/mysql, move data here OR
2. Simply create a new dir call /home/mysql, move data here

To do this edit /etc/mysql/my.cnf
...
datadir =/home/mysql
#datadir = /var/lib/mysql
...

Save the file and restart mysql and you will get the following error:

root@slave:/etc/mysql# /etc/init.d/mysql2 start
* Starting MySQL database server mysqld [fail]

The problem here is that apparmor is dissallowing mysql from running in any other location than /var/lib/mysql. You will need to edit the apparmor file for mysql.

root@slave:/etc/apparmor.d# grep "/var/lib/mysql" usr.sbin.mysqld
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,

To correct this problem replace all instances of /var/lib/mysql with /home/mysql. Open the file with file and do a search and replace. See that I am escaping the slashes!

:%s //var/lib/mysql//home/mysql/

Reload appamor and mysql should start up with the new config in your my.cnf file!

root@slave:/etc/apparmor.d# /etc/init.d/apparmor restart
Reloading AppArmor profiles : done.
root@slave:/etc/apparmor.d# /etc/init.d/mysql2 start
* Starting MySQL database server mysqld

Mysql: Alternatives to Replication w/ Remote Dumps

If you are unable to get the proper permissions/access on your system because of a shared hosting environment then you may just want to dump your local database and send it to a remote server. This is a simple way to setup a daily copy.

1. Create user called ‘remote’ on the slave (destination for backup) that you can access from the master (the data that needs to be backed up)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to remote@'MASTERIPADDRESS' IDENTIFIED BY 'PASSHERE';
Query OK, 0 rows affected (0.07 sec)

mysql> flush privileges;

Note:
*REPLICATION CLIENT Gives the right to the user to ask where the slaves/masters are.
*REPLICATION SLAVE Needed for the replication slaves (to read binlogs from master).

IMPORTANT! – Make sure /etc/mysql/my.cnf bind-address is not 127.0.0.1 – if so comment. If change, you will need to restart mysql.

2. Verify you can connect from the master to the slave.

# mysql -h ipaddresstoslave -u remote -p

3. Create a script to run the commands.

#!/usr/bin/python
import os
import datetime
file='mysql-dump-all-%s.sql' % (datetime.date.today())
print "Creating backup file..."
os.system('mysqldump --all-databases -u jester_root -pREMOTEPASS > ~/'+file)
print "Dumping to titan..."
os.system('mysql -h IPTOSLAVE -u remote -pREMOTEPASS < ~/'+file)
print "Deleting local sql file..."
os.system('rm ~/'+file)

4. Add file to cron

Done!