mkdir /var/lib/mysql-cluster/
download package: mysql-cluster-gpl-7.0.7-linux-i686-glibc23.tar.gz
tar zcf mysql-cluster-gpl-7.0.7-linux-i686-glibc23.tar.gz
mv mysql-cluster-gpl-7.0.7-linux-i686-glibc23 7_0_7
chown -R mysql.mysql 7_0_7
cd 7_0_7/data
mkdir data1 data2 data3
mkdir data1/mysql data1/test data2/mysql data2/test data3/mysql data3/test
cd ..
scripts/mysql_install_db --basedir=/var/lib/mysql-cluster/7_0_7 --datadir=/var/lib/mysql-cluster/7_0_7/data/data1
scripts/mysql_install_db --basedir=/var/lib/mysql-cluster/7_0_7 --datadir=/var/lib/mysql-cluster/7_0_7/data/data2
scripts/mysql_install_db --basedir=/var/lib/mysql-cluster/7_0_7 --datadir=/var/lib/mysql-cluster/7_0_7/data/data3
cd /var/lib/mysql-cluster/7_0_7
mkdir conf ; cd conf
::::::::::::::
config.ini
::::::::::::::
[ndbd default]
noofreplicas=2
[ndbd]
hostname=localhost
id=2
[ndbd]
hostname=localhost
id=3
[ndb_mgmd]
id = 1
hostname=localhost
[mysqld]
id=4
hostname=localhost
[mysqld]
id=5
hostname=localhost
[mysqld]
id=6
hostname=localhost
::::::::::::::
my.1.conf
::::::::::::::
[mysqld]
ndb-nodeid=4
ndbcluster=true
datadir=/var/lib/mysql-cluster/7_0_7/data/data1
basedir=/var/lib/mysql-cluster/7_0_7
port=3306
server-id=1
#log-bin
::::::::::::::
my.2.conf
::::::::::::::
[mysqld]
ndb-nodeid=5
ndbcluster=true
datadir=/var/lib/mysql-cluster/7_0_7/data/data2
basedir=/var/lib/mysql-cluster/7_0_7
port=3307
server-id=2
log-bin
::::::::::::::
my.3.conf
::::::::::::::
[mysqld]
ndb-nodeid=6
ndbcluster=true
datadir=/var/lib/mysql-cluster/7_0_7/data/data3
basedir=/var/lib/mysql-cluster/7_0_7
port=3308
server-id=3
log-bin
cd /var/lib/mysql-cluster/7_0_7
bin/ndb_mgmd --initial -f conf/config.ini --configdir=/var/lib/mysql-cluster/7_0_7/conf
bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from localhost)
id=3 (not connected, accepting connect from localhost)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @localhost (mysql-5.1.35 ndb-7.0.7)
[mysqld(API)] 3 node(s)
id=4 (not connected, accepting connect from localhost)
id=5 (not connected, accepting connect from localhost)
id=6 (not connected, accepting connect from localhost)
ndb_mgm> quit
bin/ndbd --initial -c localhost:1186
bin/ndbd --initial -c localhost:1186
bin/ndbd --initial -c localhost:1186
2009-08-31 00:19:36 [ndbd] INFO -- Configuration fetched from 'localhost:1186', generation: 1
bin/ndbd --initial -c localhost:1186
2009-08-31 00:19:39 [ndbd] INFO -- Configuration fetched from 'localhost:1186', generation: 1
bin/mysqld --defaults-file=conf/my.1.conf --user=mysql &
bin/mysqld --defaults-file=conf/my.2.conf --user=mysql &
bin/mysqld --defaults-file=conf/my.3.conf --user=mysql &
[root@centOS02 conf]# netstat -ultn | grep 330
tcp 0 0 :::3306 :::* LISTEN
tcp 0 0 :::3307 :::* LISTEN
tcp 0 0 :::3308 :::* LISTEN
bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0, Master)
id=3 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)
[mysqld(API)] 3 node(s)
id=4 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)
id=5 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)
id=6 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)
ndb_mgm> quit
[root@centOS02 7_0_7]# bin/mysql -h localhost -P 3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.35-ndb-7.0.7-cluster-gpl-log MySQL Cluster Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql> create table assets (name varchar(30) not null primary key,
-> value int) engine=ndb;
090831 0:23:33 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/assets
090831 0:23:33 [Note] NDB Binlog: logging ./test/assets (UPDATED,USE_WRITE)
090831 0:23:33 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$test/assets
090831 0:23:33 [Note] NDB Binlog: logging ./test/assets (UPDATED,USE_WRITE)
Query OK, 0 rows affected (1.98 sec)
mysql> insert into assets values ('Car','1900');
Query OK, 1 row affected (0.16 sec)
mysql> select * from assets;
+------+-------+
| name | value |
+------+-------+
| Car | 1900 |
+------+-------+
1 row in set (0.05 sec)
mysql> quit
Bye
[root@centOS02 7_0_7]# bin/mysql -h localhost -P 3307
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.35-ndb-7.0.7-cluster-gpl-log MySQL Cluster Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql> select * from assets;
+------+-------+
| name | value |
+------+-------+
| Car | 1900 |
+------+-------+
1 row in set (0.00 sec)
mysql>
2009年9月24日星期四
2009年8月26日星期三
xtrabackup - for innodb backup and restore
From Percona
Install:
$tar xzf xtrabackup-0.7.tar.gz
$cd xtrabackup-0.7
$./configure --innodb_file_io_threads=5 --innodb_file_per_table --innodb_force_recovery=4 --innodb_open_files=4096
$make
$cd innobase/xtrabackup/
$make
export PATH=$PATH:/usr/local/src/xtrabackup-0.7/innobase/xtrabackup
bakcup all databases:
innobackupex-1.5.1 --user=root --stream=tar /data/mysqlbak/ | gzip > /data/mysqlbak/bak`date +%F`_mysql.tar.gz
restore :
/etc/init.d/mysqld stop
tar zxvfi /data/mysqlbak/bak`date +%F`_mysql.tar.gz -C /data/mysql3306
/etc/init.d/mysqld start
verify
Note:
---> root@centOS01 (0.08) ^_^ # innobackupex-1.5.1 --help
Usage:
innobackup [--sleep=MS] [--compress[=LEVEL]] [--include=REGEXP] [--user=NAME]
[--password=WORD] [--port=PORT] [--socket=SOCKET] [--no-timestamp]
[--ibbackup=IBBACKUP-BINARY] [--slave-info] [--stream=tar]
[--defaults-file=MY.CNF]
[--databases=LIST] [--remote-host=HOSTNAME] BACKUP-ROOT-DIR
innobackup --apply-log [--use-memory=MB] [--uncompress] [--defaults-file=MY.CNF]
[--ibbackup=IBBACKUP-BINARY] BACKUP-DIR
innobackup --copy-back [--defaults-file=MY.CNF] BACKUP-DIR
--apply-log Prepare a backup for starting mysql server on the backup.
Expand InnoDB data files as specified in
backup-dir/backup-my.cnf, using backup-dir/ibbackup_logfile,
and create new log files as specified in
backup-dir/backup-my.cnf.
--copy-back Copy data and index files from backup directory back to
their original locations.
--remote-host=HOSTNAME
If this option is specified, backup files will be created
at the remote host by using ssh connection..
--include=REGEXP
This option is passed to the ibbackup child process.
It tells ibbackup to backup only those per-table data
files which match the given regular expression. For
each table with a per-table data file a string of the
form db_name.table_name is checked against the regular
expression. If the regular expression matches the
complete string db_name.table_name, the table is
included in the backup. The regular expression should
be of the POSIX 1003.2 "extended" form.
Try 'ibbackup --help' for more details on this option.
--databases=LIST
This option is used to specify the list of databases that
innobackup should backup. The list is of the form
"db_name[.table_name] db_name1[.table_name1] ...".
If this option is not specified all databases containing
MyISAM and InnoDB tables will be backed up.
Please make sure that --databases contains all of the
innodb databases & tables so that all of the innodb .frm
files are also backed up. In case the list is very long,
this can be specified in a file and the full path of the
file can be specified instead of the list.
--user=NAME This option is passed to the mysql child process.
It defines the user for database login if not current user.
Try 'mysql --help' for more details on this option.
--password=WORD
This option is passed to the mysql child process.
It defines the password to use when connecting to database.
Try 'mysql --help' for more details on this option.
--port=PORT This option is passed to the mysql child process.
It defines the port to use when connecting to local database
server with TCP/IP.
Try 'mysql --help' for more details on this option.
--slave-info
This option is useful when backing up a replication
slave server. It prints the binary log position and
name of the binary log file of the master server.
It also writes this information to the 'ibbackup_slave_info'
file as a 'CHANGE MASTER' command. A new slave for this
master can be set up by starting a slave server on this
backup and issuing a 'CHANGE MASTER' command with the binary
log position saved in the 'ibbackup_slave_info' file.
--socket=SOCKET
This option is passed to the mysql child process.
It defines the socket to use when connecting to local database
server with UNIX domain socket.
Try 'mysql --help' for more details on this option.
Install:
$tar xzf xtrabackup-0.7.tar.gz
$cd xtrabackup-0.7
$./configure --innodb_file_io_threads=5 --innodb_file_per_table --innodb_force_recovery=4 --innodb_open_files=4096
$make
$cd innobase/xtrabackup/
$make
export PATH=$PATH:/usr/local/src/xtrabackup-0.7/innobase/xtrabackup
bakcup all databases:
innobackupex-1.5.1 --user=root --stream=tar /data/mysqlbak/ | gzip > /data/mysqlbak/bak`date +%F`_mysql.tar.gz
restore :
/etc/init.d/mysqld stop
tar zxvfi /data/mysqlbak/bak`date +%F`_mysql.tar.gz -C /data/mysql3306
/etc/init.d/mysqld start
verify
Note:
---> root@centOS01 (0.08) ^_^ # innobackupex-1.5.1 --help
Usage:
innobackup [--sleep=MS] [--compress[=LEVEL]] [--include=REGEXP] [--user=NAME]
[--password=WORD] [--port=PORT] [--socket=SOCKET] [--no-timestamp]
[--ibbackup=IBBACKUP-BINARY] [--slave-info] [--stream=tar]
[--defaults-file=MY.CNF]
[--databases=LIST] [--remote-host=HOSTNAME] BACKUP-ROOT-DIR
innobackup --apply-log [--use-memory=MB] [--uncompress] [--defaults-file=MY.CNF]
[--ibbackup=IBBACKUP-BINARY] BACKUP-DIR
innobackup --copy-back [--defaults-file=MY.CNF] BACKUP-DIR
--apply-log Prepare a backup for starting mysql server on the backup.
Expand InnoDB data files as specified in
backup-dir/backup-my.cnf, using backup-dir/ibbackup_logfile,
and create new log files as specified in
backup-dir/backup-my.cnf.
--copy-back Copy data and index files from backup directory back to
their original locations.
--remote-host=HOSTNAME
If this option is specified, backup files will be created
at the remote host by using ssh connection..
--include=REGEXP
This option is passed to the ibbackup child process.
It tells ibbackup to backup only those per-table data
files which match the given regular expression. For
each table with a per-table data file a string of the
form db_name.table_name is checked against the regular
expression. If the regular expression matches the
complete string db_name.table_name, the table is
included in the backup. The regular expression should
be of the POSIX 1003.2 "extended" form.
Try 'ibbackup --help' for more details on this option.
--databases=LIST
This option is used to specify the list of databases that
innobackup should backup. The list is of the form
"db_name[.table_name] db_name1[.table_name1] ...".
If this option is not specified all databases containing
MyISAM and InnoDB tables will be backed up.
Please make sure that --databases contains all of the
innodb databases & tables so that all of the innodb .frm
files are also backed up. In case the list is very long,
this can be specified in a file and the full path of the
file can be specified instead of the list.
--user=NAME This option is passed to the mysql child process.
It defines the user for database login if not current user.
Try 'mysql --help' for more details on this option.
--password=WORD
This option is passed to the mysql child process.
It defines the password to use when connecting to database.
Try 'mysql --help' for more details on this option.
--port=PORT This option is passed to the mysql child process.
It defines the port to use when connecting to local database
server with TCP/IP.
Try 'mysql --help' for more details on this option.
--slave-info
This option is useful when backing up a replication
slave server. It prints the binary log position and
name of the binary log file of the master server.
It also writes this information to the 'ibbackup_slave_info'
file as a 'CHANGE MASTER' command. A new slave for this
master can be set up by starting a slave server on this
backup and issuing a 'CHANGE MASTER' command with the binary
log position saved in the 'ibbackup_slave_info' file.
--socket=SOCKET
This option is passed to the mysql child process.
It defines the socket to use when connecting to local database
server with UNIX domain socket.
Try 'mysql --help' for more details on this option.
2009年7月24日星期五
how import an excel data into mysql safely
手上来了个任务，对一些表更新，更新相应的字段都存放在excel中，如果是直接更新相应的字段倒好办，直接拼SQL就可以了，却要关联到其他二个表
自然想到把文件内容导入到 database，再处理会相对快些
and do :open the excel file and save as csv file ,and load data local infile 'x' into table tbname fields terminated by ',' lines terminated by '\r\n' ignore 1 lines;
Unfortunately-^-^这个单词拼了半天，最后还是GG了，呵
sth error,there's many common in the last column
such as
1, 2003 ,44445, http:// ,here is ok
3 ,2002 ,45645, http:// ,here has, some ,
主要过程如下：
step 1:copy excel content into editplus or ue
Note: charset
step 2:change ' " ... 为 \' \"
step 3:tab -> ","
step 4: ^(reg)->(" $(reg)->"),
step 5: set names utf8;
drop table if exists tmp;
create table tmp
(
ChID int,
CID int,
word varchar(255),
index(ChID),
index(CID),
index(Kword)
);
select "start to insert word",now();
insert into tmp values
mysql < filename
END
如果你的文件比较规范，或是没有特殊字符，不妨考虑下第一种方法，另外注意一些项
如果table是myisam
mysql> show variables like 'con%insert';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| concurrent_insert | 1 |
+-------------------+-------+
1 row in set (0.00 sec)
如果table是innodb
mysql> show variables like '%checks%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
| innodb_checksums | ON |
| unique_checks | ON |
+--------------------+-------+
3 rows in set (0.00 sec)
good luck
自然想到把文件内容导入到 database，再处理会相对快些
and do :open the excel file and save as csv file ,and load data local infile 'x' into table tbname fields terminated by ',' lines terminated by '\r\n' ignore 1 lines;
Unfortunately-^-^这个单词拼了半天，最后还是GG了，呵
sth error,there's many common in the last column
such as
1, 2003 ,44445, http:// ,here is ok
3 ,2002 ,45645, http:// ,here has, some ,
主要过程如下：
step 1:copy excel content into editplus or ue
Note: charset
step 2:change ' " ... 为 \' \"
step 3:tab -> ","
step 4: ^(reg)->(" $(reg)->"),
step 5: set names utf8;
drop table if exists tmp;
create table tmp
(
ChID int,
CID int,
word varchar(255),
index(ChID),
index(CID),
index(Kword)
);
select "start to insert word",now();
insert into tmp values
mysql < filename
END
如果你的文件比较规范，或是没有特殊字符，不妨考虑下第一种方法，另外注意一些项
如果table是myisam
mysql> show variables like 'con%insert';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| concurrent_insert | 1 |
+-------------------+-------+
1 row in set (0.00 sec)
如果table是innodb
mysql> show variables like '%checks%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
| innodb_checksums | ON |
| unique_checks | ON |
+--------------------+-------+
3 rows in set (0.00 sec)
good luck
mysql sql_mode date problem
今天在向数据仓库导数据(mysql -> oracle )时，发现有时间不正确的行，看了下，是由于mysql sql_mode的原因
mysql> use test
Database changed
mysql> SHOW VARIABLES LIKE "%sql_mode%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> create table `date` ( id integer primary key,col2 date);
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO `date` VALUES (1, "2009-12-21");
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO `date` VALUES (3, "2009-2-31");
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'col2' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from `date` where id=3;
+----+------------+
| id | col2 |
+----+------------+
| 3 | 0000-00-00 |
+----+------------+
1 row in set (0.05 sec)
mysql> SET SESSION sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `date` VALUES (4, "2009-2-31");
ERROR 1292 (22007): Incorrect date value: '2009-2-31' for column 'col2' at row 1
mysql> select * from `date` where id=4;
Empty set (0.00 sec)
mysql> SET SESSION sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `date` VALUES (4, "2009-2-31");
Query OK, 1 row affected (0.00 sec)
mysql> select * from `date` where id=4;
+----+------------+
| id | col2 |
+----+------------+
| 4 | 2009-02-31 |
+----+------------+
1 row in set (0.00 sec)
mysql> SELECT id ,col2 + INTERVAL 0 DAY FROM `date`;
+----+-----------------------+
| id | col2 + INTERVAL 0 DAY |
+----+-----------------------+
| 1 | 2009-12-21 |
| 2 | 2009-02-21 |
| 3 | NULL |
| 4 | 2009-03-03 |
+----+-----------------------+
4 rows in set (0.00 sec)
mysql> INSERT INTO `date` VALUES (5, "2008-2-31");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT id ,col2 + INTERVAL 0 DAY FROM `date`;
+----+-----------------------+
| id | col2 + INTERVAL 0 DAY |
+----+-----------------------+
| 1 | 2009-12-21 |
| 2 | 2009-02-21 |
| 3 | NULL |
| 4 | 2009-03-03 |
| 5 | 2008-03-02 |
+----+-----------------------+
5 rows in set (0.00 sec)
mysql> use test
Database changed
mysql> SHOW VARIABLES LIKE "%sql_mode%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> create table `date` ( id integer primary key,col2 date);
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO `date` VALUES (1, "2009-12-21");
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO `date` VALUES (3, "2009-2-31");
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'col2' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from `date` where id=3;
+----+------------+
| id | col2 |
+----+------------+
| 3 | 0000-00-00 |
+----+------------+
1 row in set (0.05 sec)
mysql> SET SESSION sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `date` VALUES (4, "2009-2-31");
ERROR 1292 (22007): Incorrect date value: '2009-2-31' for column 'col2' at row 1
mysql> select * from `date` where id=4;
Empty set (0.00 sec)
mysql> SET SESSION sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `date` VALUES (4, "2009-2-31");
Query OK, 1 row affected (0.00 sec)
mysql> select * from `date` where id=4;
+----+------------+
| id | col2 |
+----+------------+
| 4 | 2009-02-31 |
+----+------------+
1 row in set (0.00 sec)
mysql> SELECT id ,col2 + INTERVAL 0 DAY FROM `date`;
+----+-----------------------+
| id | col2 + INTERVAL 0 DAY |
+----+-----------------------+
| 1 | 2009-12-21 |
| 2 | 2009-02-21 |
| 3 | NULL |
| 4 | 2009-03-03 |
+----+-----------------------+
4 rows in set (0.00 sec)
mysql> INSERT INTO `date` VALUES (5, "2008-2-31");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT id ,col2 + INTERVAL 0 DAY FROM `date`;
+----+-----------------------+
| id | col2 + INTERVAL 0 DAY |
+----+-----------------------+
| 1 | 2009-12-21 |
| 2 | 2009-02-21 |
| 3 | NULL |
| 4 | 2009-03-03 |
| 5 | 2008-03-02 |
+----+-----------------------+
5 rows in set (0.00 sec)
订阅：
博文 (Atom)