Databases – Unix antiperfect explained

Databases are structures to store data. Different types exist. RDBMS (Relational DataBase Management System) is widely used. RDBMSes use SQL (Structured Query Language) as command interface. Some common commands are below. Vendors may have some specific tools or SQL-extensions on the ANSI standard. We have a page for Sybase and one for Oracle

XML: Simple (text based) way of storing an displaying data structures. More info at W3C
LDAP: Lighweight Directory Access Protocol. Sun wrote some introduction for it.
OODBMS: Object Oriented DataBase Management System

SQL

sql> show databases;: Show all databases in the server
sql> select database();: Show current database
sql> use <database>;: Make <database> current, all actions will be on by default
sql> show tables;: Show all tables in the current database
sql> describe <table>;: Show fields and keys for <table>
sql> create unique index on <table>;: Create an unique index using 2 fields
sql>ALTER TABLE <table> add column <column> varchar(255) not null after ;: Add a column to a table
sql>ALTER TABLE <table> auto_increment = 1000 ;: Auto increment value starts at 1000
sql> DROP INDEX <index> on <table>;: Drop an index
sql> SELECT * FROM <table>;: Show all rows FROM <table>
sql> SELECT <column1>,<column2> FROM <table> WHERE <column> = ‘value’;: Show specific data from a table. Where can be used in any select statement
sql> SELECT <column1>,<column2> FROM <table1> t1, <table2> t2 WHERE t2.<column> = t1.<column>;

sql> SELECT <column1>,<column2> FROM <table1> t1 INNER JOIN <table2> t2 ON t2.<column> = t1.<column>;

: Get data from one or more tables, select only those rows where the value of the columns in the where-clause are equal. The first command is a implicit inner join, the second command shows the ‘join’ format.
sql> SELECT * FROM <table1> NATURAL JOIN <table2>;: This is an inner join that can be used if the columns to match on have exactly the same name. The matching column is only showed once.
sql> SELECT * FROM <table1> INNER JOIN <table2> ON <column>;: This works like a natural join, just the matching columns is explicitly defined.
sql> SELECT <column1>,<column2> FROM <table1> t1 LEFT OUTER JOIN <table2> t2 ON t2.<column> = t1.<column>;: Get data from one or more tables, select all data from the left table (table1) and get the matching data from table2 if possible.
sql> SELECT t1.<column1>,t2.<column2> from <table1> t1 LEFT OUTER JOIN <table2> t2 ON t2.<column> = t1.<column> AND <column1> = ‘t2.value’ WHERE t1.<column2> = ‘value’ ;: Get data from one or more tables, select all data matching the WHERE clause from the left table (table1) and get the data matching the ON (including AND) clause from table2.
sql> SELECT <column1>,<column2> from <table1> t1 right outer join <table2> t2 on t2.<column> = t1.<column>;: Get data from one or more tables, select all data from the right table (table2) and get the matching data from table1 if possible.
sql> SELECT <column1>,<column2> from <table1> t1 cross join <table2> t2;: Show all possible combinations of table1 and table2. The result can be limited by using the ‘where’ clause.
sql> SELECT * from <table> where <column> IS NULL;: Null value selection (use IS NOT NULL to negate the query)
sql> SELECT * from <table> where <column> = (select ….);: Subquery, returns all rows from table where <column> is equal to the result of the subquery.
sql> SELECT count(*) from table;: Count the number of rows from the query
sql> source <filename>;

sql> . <filename>;

: Run SQL in <filename>

MySQL administration

mysqladmin processlist [ –verbose]

sql> show [full] processlist;

+—–+——+———–+——+———+——+——-+——————+

mysqladmin kill <id>

sql> kill <id>

: Kill a thread
sql> flush tables

sql> flush table <table-name>

: Close table(s), clear query cache
sql> flush tables with read lock: Flush all tables and lock them. Use if you want a consistent backup using LVM snapshot
sql> unlock tables: Like it says, don’t forget this after you created a LVM snapshot.
sql> system <Operating system command>: execute OS command
mysqlbinlog log_file | mysql: Read binary logfile and pipe it to mysql client (Update a database from a binary logfile)
sql> show binary logs: Use MySql commands to manage the logs, not OS-commands
sql> purge binary logs before “YYYY-MM-DD HH:MM:SS”: Remove the log files before the given time. Be sure everything is replicated before you remove it (if you use replication)

Set up replication

On master database server:

In my.cnf put in the [mysqld] section:

server-id = 1: Must be unique for all connected mysql servers
log-bin = 1_<hostname>-bin: Enable binary logs, if no path is given the log is in the data-directory (/var/lib/mysql)
bind-address = 0.0.0.0.: Make server listen on all interfaces to all addresses
binlog-do-db = <db_name1>,<db_name2>: Limit the the binlog to the databases you want to replicate.

sql> CREATE USER ‘repl’@’%.<domain>’ IDENTIFIED BY ‘slavepass’;: Create a user limited to the local domain.
sql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%.<domain>’;: Grant access for replication.

Make a dump of the data you already have on the master.

sql> flush tables with read lock;: Lock all tables.
sql> show master status;: Take note of the binary log file and position.

From another terminal now dump the database to replicate.

mysqldump [db_name] > dbdump.sql: Create a dump file to move to the slave.
mysqldump –all-databases –lock-all-tables –master-data > db_dump.sql: These options are also mentioned in the docs but they do not make clear if it is recommended to do so. The command should set the lock and include the SET MASTER statement.

sql> unlock tables;: Release the tables if needed.

On slave database server:

In my.cnf set in the [mysqld] section:

server-id = 2

log-bin = 2_<hostname>-bin

replicate-do-db = <db_name1>,<db_name2>

sql> change master to
-> master_host='<master hostname>’,
-> master_user='<master replication user (repl)>’,
-> master_password='<password for replication user>’,
-> mastser_log_file='<binary log file>’,
-> master_log_pos=<binary log position>;: Put config in the database, if options are omitted they remain unchanged. I think it should be put in my.cnf too for future reference. They password may be left blank in my.cnf and set using the change master command.
sql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%.<domain>’;: Grant access for replication.
sql> . db_dump.sql;: read the dump from the master
start slave;: Start slave processing. All queries in the masters binary log, starting at the given position, are executed on the slave.

Leave a Reply Cancel reply