MySQL / MariadB : Convert Database and Table to UTF8 Encoding

To convert your DB and table to UTF8:

ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

mysql 5.6 - set password

after installing MySQL 5.6.x there was no mysqladmin command for setting password.
instead i got: You will find that password in '/root/.mysql_secret'

after using the password in '/root/.mysql_secret' file to access mysql, in every command i issue i get:
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

to get things running you must set password first. to set password, do:
mysql> SET PASSWORD = password('<your new password here>');

mysql: spatial function

a few months back, i tried using mysql spatial functions

* tables with spatial columns have to be myisam
mysql> alter table lookup engine=myisam;

i created a column to contain spatial values of points:
mysql> alter table lookup add loc point not null
mysql> update lookup set loc = POINT(x, y);
mysql> create spatial index idx_loc on lookup(loc);

i created a column to contain mbr of polygons:
mysql> alter table lookup add mbr polygon not null;
mysql> update lookup set mbr = envelope(geomfromtext(concat('LINESTRING(',minx,' ',miny,',',maxx,' ',maxy,')')));
mysql> create spatial index idx_mbr on lookup(mbr);

i created a column to contain perimiter of polygons:

mysql> alter table lookup add perimiter polygon not null; mysql> update table lookup set perimeter =  geomfromtext(concat("polygon((",area,"))"));
mysql> create spatial index idx_perimiter on lookup(perimiter);

queries to search if a point is inside an area:
mysql> select name from lookup where contains(mbr,geomfromtext('point(121.08440300 14.55762000)'));
# 14 rows in set (0.16 sec)

mysql> select name from lookup where within(geomfromtext('point(121.08440300 14.55762000)'),mbr);
14 rows in set (0.02 sec)

mysql> select brgy from brgy where within(geomfromtext('point(121.08440300 14.55762000)'),perimeter);
# 2 rows in set (0.00 sec)

queries to search nearest polygon given a point:
mysql> select name, sqrt(pow(abs(x(loc)-125.08955),2)+pow(abs(y(loc)-7.00048),2)) as dist from lookup where contains(Buffer(geomfromtext('point(125.08955 7.00048)'), .05), loc) having dist < .05 order by dist limit 1;
# 1 row in set (0.01 sec)

** next improvement would be to check perimeter nearest to point since centroids are based on the dimension/shape of polygon

mysql: replication for v5.5 up - unknown command variable master-host

got unknown command variable master-host

apparently, the ff variables (with other master options) for replication in mysql configuration file my.cnf were removed starting form version 5.5

master-host=223.223.223.35
master-user=repl
master-password=replMaster
master-port=3306


instead, you must issue the ff from mysql:

CHANGE MASTER TO MASTER_HOST="223.223.223.35", MASTER_USER="repl", MASTER_PASSWORD="replMaster", MASTER_LOG_FLIE="masterdb-bin.000001", MASTER_LOG_POS=1;


you can get the master log position by issuing SHOW MASTER STATUS in your master

don't forget to start slave
START SLAVE;

mysql: replication by db and table

if you want to setup replication in mysql for specific db only...

you can setup your master as:
# master

[mysqld]
port=3306
socket=/var/lib/mysql/mysql.sock

local-infile=1
server-id=1
log-bin
binlog-do-db=test_db

and your slave as:
# slave

[client]
port=3306
socket=/var/lib/mysql/mysql.sock

[mysqld]
port=3306
socket=/var/lib/mysql/mysql.sock

server-id=2
replicate-do-db=test_db


for replication of a specific table, use:

replicate-do-db=test_db.table1

mysql: recover from sql replication error

if you encounter a replication error caused by one sql command, you can skip that error and continue your replication by running the following commands on your slave:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
mysql> start slave;


mysql: error code 1034 incorrect key file for table

this error was displayed while trying to access one of my tables

MySQL error code 1034 (ER_NOT_KEYFILE): Incorrect key file for table '%-.200s'; try to repair it

both # myisamcheck -o <table name> and mysql> repair table <table name>; didn't work.

had to recreate my table from an sql dump file xP

mysql: query having condition

do you know that you can query with a condition that subjects a result in your selection?

ex. i want to know the character length of a field, so i do a...

mysql> select length(myfield) from mytable;

now if i want to get only those with length exceeding 160 characters, i do a...

mysql> select length(myfield) from mytable where length(myfield) > 160;

right. but have you tried 'having' command? apparently, you can also do a...

mysql> select length(myfield) as fieldlength from mytable having fieldlength > 160;

if you have a very complicated select formula, i'm sure you'll greatly appreciate not having to rewrite it accurately in the where portion of your query

yun lang ;)

mysql: cant remote 1042

if you get a 'cant remote' 1042 in mysql

add this to your mysql configuration file my.cnf:
[mysqld]
skip-name-resolve

mysql: server installation - quit without updating file error

can't install MySQL server on CentOS

MySQL... ERROR! Manager of pid-file quit without updating file.

disable selinux in /etc/selinux/config

mysql: skip replication error

if your replication halts because of an error, you can skip replication errors and continue replication

here's the command to skip 1 error then continue
# set global sql_slave_skip_counter=1; start slave;

mysql: recover from replication error

to recover from replication error... do the ff:

* if you can stop master for a few mins

1. stop current replication db
(on slave db)

# show slave status;
# stop slave;
# reset slave;

> /etc/init.d/mysql stop

2. stop master mysql
(on master db)

# show master status;
# reset master;

> /etc/init.d/mysql stop

3. get a snapshot of master db
(on master db)

> copy -r /data/mysqldata /home/backup/mysqldataMaster

4. start master db

> /etc/init.d/mysql start

# show master status;

5. copy snapshot of master db to slave db

> scp ...
> cp ...

6. start slave and replication

> /etc/init.d/mysql start

# start slave;
# show slave status;

** if you cant stop master for a few mins

1. stop current replication db
(on slave db)

# show slave status;
# stop slave;
# reset slave;

> /etc/init.d/mysql stop

2. reset master mysql
(on master db)

# show master status;
# reset master;

3. get a snapshot of master db
(on master db)

> copy -r /data/mysqldata /home/backup/mysqldataMaster

4. copy snapshot of master db to slave db

> scp ...
> cp ...

5. start slave and replication

> /etc/init.d/mysql start

# start slave;
# show slave status;

*** if you just want recover replication without stopping mysql (and without copying missing entries)

1. stop current replication db
(on slave db)

# show slave status;
# stop slave;
# reset slave;

2. reset master mysql
(on master db)

# show master status;
# reset master;

3. start slave and replication

# start slave;
# show slave status;

gdal: update supported formats

to update gdal's supported formats, extract compressed source then configure with support options. i use the options below which include support for postgresql and mysql
./configure --with-jpeg --with-gd --with-freetype --with-png --with-ogr --with-proj --with-gdal --with-httpd=/usr/local/apache2 --with-tiff --with-wfs --with-wcs --with-threads --with-wmsclient --with-wfsclient --with-geos=/usr/local/bin/geos-config --with-postgis --enable-debug --with-pg=/usr/local/pgsql/bin/pg_config --with-gif --with-mysql --with-php-- --with-libtiff --with-static-proj4=/usr/local/bin

to check gdal's supported formats
# ogrinfo --formats
Supported Formats:
  -> "ESRI Shapefile" (read/write)
  -> "MapInfo File" (read/write)
  -> "UK .NTF" (readonly)
  -> "SDTS" (readonly)
  -> "TIGER" (read/write)
  -> "S57" (read/write)
  -> "DGN" (read/write)
  -> "VRT" (readonly)
  -> "REC" (readonly)
  -> "Memory" (read/write)
  -> "BNA" (read/write)
  -> "CSV" (read/write)
  -> "GML" (read/write)
  -> "GPX" (read/write)
  -> "KML" (read/write)
  -> "GeoJSON" (read/write)
  -> "GMT" (read/write)
  -> "PostgreSQL" (read/write)
  -> "MySQL" (read/write)
  -> "AVCBin" (readonly)

mysql: fulltext index

to create a fulltext in mysql

# make sure your table is a MYISAM table
ALTER TABLE maps ENGINE = MYISAM;

# create a fulltext index on your column
CREATE fulltext INDEX idx_name ON tablename(columnname);

# to search
SELECT * FROM tablename WHERE MATCH(columnname) AGAINST("+word1 +word2" IN BOOLEAN MODE);

mysql: dump mysql database or table to a file

i usually use mysqldump to backup databases or to get a snapshot for propagation

to dump mysql database to a file
# mysqldump -u user -p password <database name> > dumpfile

to dump a specific table to a file (in case you want to bakup/propagate just one table)
# mysqldump -u user -p password <database name> <table name> > dumpfile

you can add --opt for optimized dump with 'drop table if exists' etc...

to load mysql dump file
# mysql -u user -p password <database name> < dumpfile

mysql: replication

# in master db's my.cnf
[mysqld]
port=3306
socket=/var/lib/mysql/mysql.sock
datadir=/data1/mysqldata
 
set-variable = key_buffer_size=1500M
set-variable = max_allowed_packet=1M
set-variable = max_connections=1200
set-variable = max_connect_errors=999999
set-variable = max_user_connections=1200
set-variable = table_cache=512
set-variable = sort_buffer=128M
set-variable = ft_min_word_len=2
 
local-infile=1
server-id=1
log-bin
 
[mysqldump]
quick

# in slave db's my.cnf
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
 
[mysqld]
port=3306
socket=/var/lib/mysql/mysql.sock
datadir=/data1/mysqldata
set-variable = key_buffer_size=512M
set-variable = max_allowed_packet=1M
set-variable = max_connections=1200
set-variable = max_connect_errors=999999
set-variable = max_user_connections=1200
set-variable = table_cache=512
set-variable = sort_buffer=128M
set-variable = max_binlog_size=512M
 
slave-skip-errors=1062,1114,1050,1051,1146
 
local-infile=1
server-id=2
master-host=223.223.223.35
master-user=repl
master-password=replMaster
master-port=3306
 
[mysqldump]
quick

# grant access for slave db to master db

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<slave_password>';

# dont forget to flush privileges
FLUSH PRIVILEGES;

bluedragon: datasource with mysql connector

access http://host:8080/bluedragon for datasource administration through admin interface

to install mysql connector...

 ...in linux:
1. download mysql java connector file (mysql-connector-java-3.0.17-ga.tar.gz)
2. unzip compressed file
3. copy mysql-connector-java.jar to bluedragon_path/lib/mysql.jar
4. restart bluedragon
5. create datasource

...in windows:
1. install mysql odbc driver (odbc 3.51 driver)
2. add system dsn
3. refresh bluedragon datasources
4. create datasource

mysql: fix crashed table error 127

to check error code details, use perror
# perror 127

to fix, use myisamchk
# myisamchk -r *.MYI

then refresh your tables by issuing a flush
mysql> flush tables;

mysql: alter table capacity

got a 'table is full' error in mysql? that means you've reached the size limit for a table.
you can solve this by deleting data from your table or adjusting your table's capacity.

here's how to adjust your table's capacity:

1. take a look at your table's status
mysql> show table status like '<table name>' \G

2. adjust your max_data_length, which is currently set to 4GB
mysql> alter table <table_name> max_rows = 200000000000;

now, take a look at your table's status again and see that your max_data_length changed

if want to go the other way, i know you know how to delete data by yourself ;)

mysql: age-based purging of binary logs

how to purge old binary logs?

note: if you use your binary logs for replication make sure your slave db server is replicating and is up to date before you start purging. you can do that by issuing:

slave mysql> show slave status \G

see a list of binary logs on your master db server:

master mysql> show binary logs;

then manually purge logs as you want (i drop 3 month old logs):

master mysql> purge master logs before adddate(Now(), interval -90 day);

or you can automatically do this through a system variable in your mysql configuration file:

expire_logs_days = 90

(i have yet to try this)

you can also purge logs by deleting log files from your master db server's mysql directory. just make sure that the files you delete are not the ones currently used by your master and slave db servers.

why purge your binary logs? hard disk drives may be cheap these days, but i ain't buyin!!