oracle 用户密码为何不可以以数字开头

哪位了解详解的，来说说

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.

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

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)

oracle领地

AskTom

Metalink

Oracle OTN Forum