Navigation

MySql download: http://www.mysql.com/downloads/mysql/
Clean / Remove current MySql instalation: yum remove mysql
Un-tar the mysql tar.gz: tar -xzf Desktop/mysql-5.1.53-linux-i686-glibc23.tar.gz
Create a simbolic link to the instalation dir: ln -s mysql-5.1.53-linux-i686-glibc23 mysql
cd mysql
scripts/mysql_install_db

show index from \G
mysqlslap -uusername -p -a -i 10 --concurrency=1,4,16,64,256
show processlist;

Infobright Enterprise Edition (column level compression - open source)

Index prefix: indexing only a subset of a filed (let's say the first 4 characthers) to save on index size and improve performance.

Explaining a query with a sub-query will execute the subquery.

show status;
flash status;

Daily Notes:
http://bit.ly/OUAttendance
O'Reilly MySql High Performance (2nd Eddition)
QAN: Query Analyzer
Benchmarking Tool: mysqlslap

Create 2 databases, my1 and my2, my1 will be the master and will replicate to my2.

Install my5.1 into c:\my51
In windoes, set the env variable for the new MySql instance.

Create my1:

mkdir c:/data1
mkdir c:/log1
create c:/my1.cnt

In notepad add to c:/my1.cnt:
   [mysql]
   datadir=c:/data1
   basedir=c:/my51
   port=5011
   log-bin=c:/log1/inst1
   #unique id for each instance in replication topology
   server-id=1

navigate to the my51/bin dir and type: mysqld --defaults-file=c:\my1.cnf --skip-grant-tables
Connect to the instanse (another commandline window): mysql -P 5011 (notice the upper case P)
Create the MySql db: create database mysql;
Navigate to the MySql db: use mysql;
Run sys scripts:
   source c:\my51\share\mysql_system_tables.sql
   source c:\my51\share\mysql_system_tables_data.sql
   source c:\my51\share\fill_help_tables.sql (if help files/data/info is needed)

To shutdown the instanse, exit out of the instanse into the OS: mysqladmin shutdown -P 5011
navigate to the my51/bin dir and type: mysqld --defaults-file=c:\my1.cnf
Connect to the instanse (another commandline window): mysql -P 5011 -u root (notice the upper case P and -u for the root user)

Create a replication user on the master:
   create user repl@localhost identified by 'repl';
   grant replication slave on *.* to repl@localhost;

Create my2:

mkdir c:/data2
mkdir c:/log2
create c:/my2.cnt

In notepad add to c:/my2.cnt:
   [mysql]
   datadir=c:/data2
   basedir=c:/my51
   port=5022
   log-bin=c:/log2/inst2 (set if you want to replicate my3 from my2 vs replicating from master my1)
   log_slave_update=on (set if you want to replicate my3 from my2 vs replicating from master my1)
   #unique id for each instance in replication topology
   server-id=2

navigate to the my51/bin dir and type: mysqld --defaults-file=c:\my2.cnf --skip-grant-tables
Connect to the instanse (another commandline window): mysql -P 5022 (notice the upper case P)
Create the MySql db: create database mysql;
Navigate to the MySql db: use mysql;
Run sys scripts:
   source c:\my51\share\mysql_system_tables.sql
   source c:\my51\share\mysql_system_tables_data.sql
   source c:\my51\share\fill_help_tables.sql (if help files/data/info is needed)

To shutdown the instanse, exit out of the instanse into the OS: mysqladmin shutdown -P 5022
navigate to the my51/bin dir and type: mysqld --defaults-file=c:\my2.cnf
Connect to the instanse (another commandline window): mysql -P 5022 -u root (notice the upper case P and -u for the root user)

Prep the slave for the master:
run: show master status on the master to see what logile to use and at what possition on the log file to start replicate.
change master to master_host = 'localhost', master_user='repl', master_password='repl', master_port=5011, master_log_file='inst1.000002', master_log_pos=329;
start slave;
show slave status\G

MySql Class Instructur Notes (MySqlClassInstructorNotes.txt)
MySql 5.1 Installation (mysql-noinstall-5.1.51-win32.zip)

Share Drive: \\10.12.152.156\Share\

InoDB = only one tablespase.

*.err logs are in the same directory as the data/database/tables.

connect to mysql: mysql -u root -p

show databases; - each database is just a directory.
show full tables;

to go into a database: use

*.frm - data dictionary for the table. (every table must have one, "show table" simply does an ls on the directory)
*.MYD - data file for MyIsam engine.
*.MYI - Indexes for MyIsam engine.

Dropping these 3 files effectivly drops the table.

Database called "information_schema" holds metadata/dicrionary.
"mysql" database is also a data dictionary.

To migrate or copy tables you simply need to copy and paste the *.frm, *.MYD, *.MYI, same for the DB with a directory.

Percona - Oracle compatitors for MySql support.
MariaDB - Monty's thing....Monty Program AB.
Drizzle - Complete fork, stripped down MySql 6.0.

DO NOT USE:
MySql Migration Toolkit.
MySql Administrator.
MySql Query Prowser.

Do use:
MySql Workbench. (free)
PHPAdmin.

MySql Proxy - Can direct different type of queries to diferent type of slaves, can be used as a simple load balancer.

Create a database from file:
create database world;
use world
source c:\world.sql

Single row vertical select: select * from my_table \G

mysqld --help --verbose

mysql -e "select * from dual"

mysql --batch -e "select * from world.city" (will remove the borders and such from the query result on the client)
mysql --xml -e "select * from world.city" (will output an XML structured data)

set global = (changes a dynamic variable value)

"set session" is also available. (set sesson always overwrite set global)

set global general_log = on (logs everything before parsing)

show global status - show global statistics for a the instance for performance reasons.

show create table

----------------------------------------------------------------------------------------------------------

show create table ; - will show the create statment for a table.

spesify the number of records expected to be in a table to increase performance.

select @@sql_mode;
select @@autocommit;

set sql_mode = '';

status; - will show info on the current connection db.

show table status like ''\G

Max_data_length: 281474976710655

alter table max_rows = 4000;

explain \G
explain partitions \G

show engines;

start transaction;
update...
delete...
insert....
commit; / rollback;

show create view ;
A text of the view is save in an *.frm file.

show full processlist;

mk-table-checksum - a good tool to compare tables b/t database / replication.

After a crash, queries that uses indexes may return wrong results, to verify, run the query with no indexes, and/or do a checksum with the slave table.

NO_WRITE_TO_BINLOG - prevent the statment to get in to the logs and replicate to the slaves.

event = dmbs_job