Export MySQL query results in csv format

select *
from table
where .....
INTO OUTFILE '/tmp/output.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

mysqlbinlog: unknown variable 'default-character-set=utf8'

Today when I try to check the log-bin file, encounter an error:
mysqlbinlog: unknown variable 'default-character-set=utf8'
it's because inside the my.cnf  got
default-character-set=utf8


the default-character-set is deprecated in 5.5. we should use instead:

character-set-server = utf8

 

or add ----no-defaults

 

mysqlbinlog --no-defaults -v logbin-log.000003 > logbin003.sql

 

-----------------------The End----------------------------

MySQL 5.6 GTID replication "doesn't exists on query" error fix

Master-slave environment encounter error 1858, it's happening when drop an object on master, but the object doesn't on slave , this is the error:

 Last_SQL_Error: Error 'Can't drop database 'dba1'; database doesn't exist' on query. Default database: 'dba1'. Query: 'drop database dba1'
firstly I try to skip this error:
mysql> set global sql_slave_skip_counter=1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
Oops, GTID can't use sql_slave_skip_counter any more, try to generate a null transaction to skip the error, check the binlog file and get the last gtid
mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| log-bin.000020 |       196 |
| log-bin.000021 |      1119 |
+----------------+-----------+
2 rows in set (0.00 sec)
mysql> exit
Bye
[dbaroot@dba ~]$ su -
Password:
[root@dba ~]# mysqlbinlog /usr/local/mysql/data/log-bin.000021

Run next on slave MySql

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> set session gtid_next='6433e872-0fad-11e3-a916-00155d01c300:24';
Query OK, 0 rows affected (0.00 sec)
mysql> begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set session gtid_next=AUTOMATIC;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
Show slave status\G again, that error transaction skip already, master-slave working fine.
-------------------------------the end-------------------------------------------

Installing EPEL repository on Centos 6

[root@dba local]# wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
--2013-11-07 13:17:22--  http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Resolving dl.fedoraproject.org... 209.132.181.26, 209.132.181.27, 209.132.181.23, ...
Connecting to dl.fedoraproject.org|209.132.181.26|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 14540 (14K) [application/x-rpm]
Saving to: “epel-release-6-8.noarch.rpmâ€
100%[=======================================================================================================================================>] 14,540      --.-K/s   in 0.002s
2013-11-07 13:17:22 (5.76 MB/s) - “epel-release-6-8.noarch.rpmâ€
[root@dba local]# ls -la
total 297256
drwxr-xr-x. 13 root  root       4096 Nov  7 13:17 .
drwxr-xr-x. 13 root  root       4096 Nov  5 07:53 ..
drwxr-xr-x.  2 root  root       4096 Sep 23  2011 bin
-rw-r--r--.  1 root  root      14540 Nov  5  2012 epel-release-6-8.noarch.rpm
drwxr-xr-x.  2 root  root       4096 Sep 23  2011 etc
drwxr-xr-x.  2 root  root       4096 Sep 23  2011 games
drwxr-xr-x.  2 root  root       4096 Sep 23  2011 include
drwxr-xr-x.  2 root  root       4096 Sep 23  2011 lib
drwxr-xr-x.  2 root  root       4096 Sep 23  2011 lib64
drwxr-xr-x.  2 root  root       4096 Sep 23  2011 libexec
drwxr-xr-x. 13 mysql mysql      4096 Nov  7 11:39 mysql
drwxr-xr-x.  2 root  root       4096 Sep 23  2011 sbin
drwxr-xr-x.  5 root  root       4096 Nov  5 07:53 share
drwxr-xr-x.  2 root  root       4096 Sep 23  2011 src
[root@dba local]# rpm -Uvh epel-release-6-8.noarch.rpm
warning: epel-release-6-8.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing...                ########################################### [100%]
   1:epel-release           ########################################### [100%]
[root@dba local]# ls -lah /etc/yum.repos.d/ | grep epel
-rw-r--r--.  1 root root  957 Nov  5  2012 epel.repo
-rw-r--r--.  1 root root 1.1K Nov  5  2012 epel-testing.repo
------------------The end-------------------------------------------------------------------

MySQL export table to csv file

SELECT id, table_id, indices,create_time
INTO OUTFILE '/usr/local/mysql/tblTest.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM tblTest WHERE 1;

CENTOS install rlwrap for oracle sqlplus

[root@localhost6 ~]# rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Retrieving http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
warning: /var/tmp/rpm-tmp.rIN0Eg: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing...                ########################################### [100%]
   1:epel-release           ########################################### [100%]
[root@localhost6 ~]# rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
[root@localhost6 ~]# yum install rlwrap readline readline-devel
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
epel/metalink                                                                                                                                             | 5.6 kB     00:00
 * base: mirrors.sin3.sg.voxel.net
 * epel: mirror.smartmedia.net.id
 * extras: mirrors.sin3.sg.voxel.net
 * updates: mirror.colocity.com
epel                                                                                                                                                      | 4.2 kB     00:00
epel/primary_db                                                                                                                                           | 5.6 MB     00:00
Setting up Install Process
Package readline-6.0-4.el6.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package readline-devel.x86_64 0:6.0-4.el6 will be installed
--> Processing Dependency: ncurses-devel for package: readline-devel-6.0-4.el6.x86_64
---> Package rlwrap.x86_64 0:0.37-1.el6 will be installed
--> Running transaction check
---> Package ncurses-devel.x86_64 0:5.7-3.20090208.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=================================================================================================================================================================================
 Package                                       Arch                                  Version                                           Repository                           Size
=================================================================================================================================================================================
Installing:
 readline-devel                                x86_64                                6.0-4.el6                                         base                                134 k
 rlwrap                                        x86_64                                0.37-1.el6                                        epel                                 90 k
Installing for dependencies:
 ncurses-devel                                 x86_64                                5.7-3.20090208.el6                                base                                642 k
Transaction Summary
=================================================================================================================================================================================
Install       3 Package(s)
Total download size: 866 k
Installed size: 2.2 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): ncurses-devel-5.7-3.20090208.el6.x86_64.rpm                                                                                                        | 642 kB     00:00
(2/3): readline-devel-6.0-4.el6.x86_64.rpm                                                                                                                | 134 kB     00:00
(3/3): rlwrap-0.37-1.el6.x86_64.rpm                                                                                                                       |  90 kB     00:00
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                            482 kB/s | 866 kB     00:01
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
  Installing : ncurses-devel-5.7-3.20090208.el6.x86_64                                                                                                                       1/3
  Installing : readline-devel-6.0-4.el6.x86_64                                                                                                                               2/3
  Installing : rlwrap-0.37-1.el6.x86_64                                                                                                                                      3/3
  Verifying  : ncurses-devel-5.7-3.20090208.el6.x86_64                                                                                                                       1/3
  Verifying  : readline-devel-6.0-4.el6.x86_64                                                                                                                               2/3
  Verifying  : rlwrap-0.37-1.el6.x86_64                                                                                                                                      3/3
Installed:
  readline-devel.x86_64 0:6.0-4.el6                                                          rlwrap.x86_64 0:0.37-1.el6
Dependency Installed:
  ncurses-devel.x86_64 0:5.7-3.20090208.el6
Complete!
[root@localhost6 ~]#


#Login as oracle user
vi /home/oracle/.bash_profile

#append 2 more lines
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'

Logout and login again.

--------------------The End----------------------------------------------------------------------

MySQL NDB Cluster Configuration Step by Step

To set up the cluster, I create 5 Hyper-V virtual machine with CENTOS 6.4, one management node, two data node and two mysql node.
192.168.1.144 (mgm)
192.168.1.196 (ndb)
192.168.1.197 (ndb)
192.168.1.152 (mysqld)
192.168.1.193 (mysqld)
Step1:
Download mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64.tar.gz  binary file from mysql website http://dev.mysql.com/downloads/cluster/
Copy the download file to those 5 Linux server and do the next step on all 5 servers.
groupadd mysql
useradd –g mysql mysql
tar –zxvf  /usr/local/ mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64.tar.gz
mv /usr/local/ mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64 /opt/mysql_cluster
chown mysql.mysql –R /opt/mysql_cluster
On server  192.168.1.196 and 192.168.1.197 create a new folder
mkdir  /opt/mysql_cluster/ndbdata
chown mysql.mysql –R /opt/mysql_cluster


Step2:
Mgm Node(192.168.1.144) configuration
cd /opt/mysql_cluster/
vi config.ini

[root@CENTOS2 ~]# cat /opt/mysql_cluster/config.ini
 [ndbd default]
 NoOfReplicas= 2
 [NDB_MGMD]
 nodeid=1
 HostName= 192.168.1.144
 [NDBD]
 nodeid=11
 HostName= 192.168.1.196
 Datadir= /opt/mysql_cluster/ndbdata
 [NDBD]
 nodeid=12
 HostName= 192.168.1.197
 Datadir= /opt/mysql_cluster/ndbdata
 [MYSQLD]
 nodeid=21
 HostName= 192.168.1.152
 [MYSQLD]
 nodeid=22
 HostName= 192.168.1.193

Step3:
Install and configure mysql on two mysqld node (192.168.1.152, 192.168.1.193)
cd /opt/mysql_cluster/
scripts/mysql_cluster_install --user=mysql --datadir=/opt/mysql_cluster/data --basedir=/opt/mysql_cluster/
mv /opt/mysql_cluster/my.cnf /etc/my.cnf
vi /etc/my.cnf

[mysqld]
 ndbcluster
 ndb-connectstring=192.168.1.144
 [mysql_cluster]
 ndb-connectstring=192.168.1.144

Modify the /opt/mysql_cluster/support-files/mysql.server, replace datadir and basedir
datadir=/opt/mysql_cluster/data
basedir=/opt/mysql_cluster/
Step 4:
Start all cluster and mgm node
Startup data node(192.168.1.196, 192.168.1.197)
[root@Node1]# /opt/mysql_cluster/bin/ndbd --defaults-file=/opt/mysql_cluster/my.cnf --initial
2013-10-02 13:17:36 [ndbd] INFO     -- Angel connected to '192.168.1.144:1186'
2013-10-02 13:17:36 [ndbd] INFO     -- Angel allocated nodeid: 11
Startup mysqld node(192.168.1.152, 192.168.1.193)

Startup the mgm node
/opt/mysql_cluster/bin/ndb_mgmd -f /opt/mysql_cluster/config.ini --configdir=/opt/mysql_cluster
Step 5:
Check status

-----------------------------------------The End---------------------------------------------------------------