MySQL backup & binlog

- Full backup (dump tables to sql file)

shell> mysqldump --user=xxx --password=xxx --single-transaction --flush-logs --master-data --delete-master-logs --all-databases -r backup_sunday_1_PM.sql

- Differential backup (take binlog file)

shell> mysqladmin flush-logs

MySQL binary logs provide point-in-time recovery. But it may take huge disk space.

flush-logs: close the current binary log file and begin a new one
delete-master-logs: delete binary logs
master-data: write binary log information to dump file, so we know which is the next position in binlog. E.g., in dump file, we see

--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='hwm_binlog.000002', MASTER_LOG_POS=98;

And mysqlbinlog can be used to display the content of binlog.

C:\HWM\mysqldb\bin>mysqlbinlog ..\data\hwm_binlog.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#110830 23:16:33 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.
24-community-nt-log created 110830 23:16:33
# Warning: this binlog was not closed properly. Most probably mysqld crashed wri
ting it.
# at 98
#110830 23:16:33 server id 1  end_log_pos 198   Query   thread_id=108   exec_tim
e=0     error_code=0
use webnmsdb;
SET TIMESTAMP=1314760593;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq
ue_checks=1;
SET @@session.sql_mode=0;
/*!\C utf8 */;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio
n.collation_server=8;
DELETE FROM `webnmsdb`.`extstats`;
# at 198

The recovery of crashed InnoDB

If the database crashes, InnoDB will attempt to fix everything by running the log file from the last timestamp. Unfortunately if InnoDB fails to repair itself, the -entire- database will not start. MySQL will exit with an error message and your entire database will be offline.

Once you have corrupt InnoDB tables that are preventing your database from starting, you should follow this five step process:

Step 1: Add this line to your /etc/my.cnf configuration file:

[mysqld]
innodb_force_recovery = 4

Step 2: Restart MySQL. Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored.

Step 3: Dump all tables

Step 4: Shutdown database and delete the data directory. Run mysql_install_db to create MySQL default tables

Step 5: Remove the innodb_force_recovery line from your /etc/my.cnf file and restart the database. (It should start normally now)

Step 6: Restore everything from your backup

*** This is copied from How to Fix MySQL Database (MyISAM / InnoDB). The command mysql_install_db is only available since MySQL 5. The steps I took are slightly differently since we are using MySQL 4.
1. Set innod_force_recovery=6
2. Start MySQL, drop databases
3. Stop MySQL, delete ibdata and ib_logfiles, and unset innodb_force_recovery
4. Start MySQL again, the tablespace and log files will be recreated
5. Restore the data with the backup file

MySQL Commands

- Enable/disable MySQL general log (query log)

  % set global general_log = 'ON'   (or 'OFF')

The file is output to hostname.log by default or we can specify file name in my.ini with "log=fileName"

- Turn on/off Foreign Key checks for dropping schema

  % set foreign_key_checks = 1;  (or 0 to turn off)

- Check version

  % status;

MySQL InnoDB failed

The InnoDB storage engine failed to start so all tables were created as MyISAM tables with MySQL’s strange behaviour. As a result, all FK constraints were removed and crashed the server thereafter. (Tip: use "show create table xxx" to check the created table schema.) It seems that during installation, MySQL is not able to create innodb temporary file as shown in error logged in the file mysql\data\.err.

101006 14:36:46 InnoDB: Error: unable to create temporary file; errno: 2
101006 14:36:46 [ERROR] Plugin 'InnoDB' init function returned error.
101006 14:36:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

The reason is that when innodb starts the tmpdir defined in my.ini should have existed.

Drop database from DOS batch script

mysql -uroot -pabc123 -e "drop database netsight" >nul 2>1&

The option -e to run execute a command. --help (-?) to display help information.

Redirect to null to keep quiet. Otherwise, it may display an error "ERROR 1010 (HY000) at line 1: Error dropping database (can't rmdir '.\netsight', errno: 41)". Since when dropping a database, it will try to delete folder mysql/data/netsight, it fails if the folder contains some non-db files.

Insert for multiple records

insert into ce_antennaSocket(platformName, socketID, socketName, radioBandID) values
('AP2600', '0', 'Left Antenna Type', 0), 
('AP2600', '1', 'Right Antenna Type', 0), 
('AP2650', '0', 'Top Left Antenna Type', 0), 
('AP2650', '1', 'Bottom Left Antenna Type', 0), 
('AP2650', '2', 'Top Right Antenna Type', 0), 
('AP4102', '1', 'Right Antenna Type', 1);

It has much much better performance than split into multiple insert statements. To insert 15k records, it took less than 1 second. Otherwise, it took 8 minutes.

MySQL sql udpate - multiple table syntax

update polleddata pd set pd.absolutelastcountervalue=
( select lastcountervalue 
  from v2r2_temp_tbl1 vtt 
  where vtt.id=pd.id);

will run 15 minutes for 8000+ records. Change it to:

update polleddata pd, v2r2_temp_tbl1 vtt 
set pd.absolutelastcountervalue=vtt.lastcountervalue 
where pd.id=vtt.id;

has much better performance.

MySQL

- MySQL has ENUM type, it is used to enforce the data integrity and de-normalize table. Those joins may add unnecessary complexity to your database.

create table reservations (
   reservation_id int unsigned auto increment primary key,
   ...
   seat_pref_description enum('Window', 'Aisle')
);

Should a user place a invalid value to this column, MySQL will block the incorrect entry and place NULL instead.

- CHAR and VARCHAR have maximum length 255 limitation, can followed by keyword BINARY, mean string comparison case sensitive. Longer string is stored as TEXT type or BLOB( for binary data).

- Two (or more) storage engines: INNODB, MYISAM. The default engine can be specified with parameter default-storage-engine in my.ini.
INNODB: support transaction safe, row-level locking and foreign key
MYISAM: faster, and support full-text searching

create table example(field1 int, field2 varchar(30)) engine=MYISAM;