分类目录归档：MySQL

一、网友需求
文档中有类此如a b c0e f g0i j k0x f f的字符串，需要以0分行，以空格分列导入到mysql数据库的一张只有三个列的表中

二、mysqlimport使用说明

mysqlimport  Ver 3.7 Distrib 5.5.9, for Win32 (x86)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Loads tables from text files in various formats.  The base name of the
text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open and
read the text file directly. In other cases the client will open the text
file. The SQL command 'LOAD DATA INFILE' is used to import the rows.

Usage: mysqlimport [OPTIONS] database textfile...
Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 5.5\my.ini C:\Program Files\MySQL\MySQL Server 5.5\my.cnf 
The following groups are read: mysqlimport client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
  --character-sets-dir=name 
                      Directory for character set files.
  --default-character-set=name 
                      Set the default character set.
  -c, --columns=name  Use only these columns to import the data to. Give the
                      column names in a comma separated list. This is same as
                      giving columns to LOAD DATA INFILE.
  -C, --compress      Use compression in server/client protocol.
  -#, --debug[=name]  Output debug log. Often this is 'd:t:o,filename'.
  --debug-check       Check memory and open file usage at exit.
  --debug-info        Print some debug info at exit.
  -d, --delete        First delete all rows from table.
  --fields-terminated-by=name 
                      Fields in the input file are terminated by the given
                      string.
  --fields-enclosed-by=name 
                      Fields in the import file are enclosed by the given
                      character.
  --fields-optionally-enclosed-by=name 
                      Fields in the input file are optionally enclosed by the
                      given character.
  --fields-escaped-by=name 
                      Fields in the input file are escaped by the given
                      character.
  -f, --force         Continue even if we get an SQL error.
  -?, --help          Displays this help and exits.
  -h, --host=name     Connect to host.
  -i, --ignore        If duplicate unique key was found, keep old row.
  --ignore-lines=#    Ignore first n lines of data infile.
  --lines-terminated-by=name 
                      Lines in the input file are terminated by the given
                      string.
  -L, --local         Read all files through the client.
  -l, --lock-tables   Lock all tables for write (this disables threads).
  --low-priority      Use LOW_PRIORITY when updating the table.
  -p, --password[=name] 
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -W, --pipe          Use named pipes to connect to server.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -r, --replace       If duplicate unique key was found, replace old row.
  --shared-memory-base-name=name 
                      Base name of shared memory.
  -s, --silent        Be more silent.
  -S, --socket=name   The socket file to use for connection.
  --ssl               Enable SSL for connection (automatically enabled with
                      other flags).
  --ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
                      --ssl).
  --ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
  --ssl-cert=name     X509 cert in PEM format (implies --ssl).
  --ssl-cipher=name   SSL cipher to use (implies --ssl).
  --ssl-key=name      X509 key in PEM format (implies --ssl).
  --ssl-verify-server-cert 
                      Verify server's "Common Name" in its cert against
                      hostname used when connecting. This option is disabled by
                      default.
  --use-threads=#     Load files in parallel. The argument is the number of
                      threads to use for loading data.
  -u, --user=name     User for login if not current user.
  -v, --verbose       Print info about the various stages.
  -V, --version       Output version information and exit.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
character-sets-dir                (No default value)
default-character-set             auto
columns                           (No default value)
compress                          FALSE
debug-check                       FALSE
debug-info                        FALSE
delete                            FALSE
fields-terminated-by              (No default value)
fields-enclosed-by                (No default value)
fields-optionally-enclosed-by     (No default value)
fields-escaped-by                 (No default value)
force                             FALSE
host                              (No default value)
ignore                            FALSE
ignore-lines                      0
lines-terminated-by               (No default value)
local                             FALSE
lock-tables                       FALSE
low-priority                      FALSE
port                              3306
replace                           FALSE
shared-memory-base-name           (No default value)
silent                            FALSE
socket                            (No default value)
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
use-threads                       0
user                              (No default value)
verbose                           FALSE

三、模拟实现网友需求
1、创建一张import表，结构如下

mysql> desc xifenfei.import;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | char(1) | YES  |     | NULL    |       |
| b     | char(1) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.02 sec)

2、在D盘根目录下创建一个import.data文件，内容如下
a b c0e f g0i j k0x f f

3、实现导入

C:\Users\XIFENFEI>mysqlimport -uroot -pxifenfei --fields-terminated-by=" "  --lines-terminated-by="0" --columns=a,b,c -d xifenfei d:/import.data 
xifenfei.import: Records: 4  Deleted: 0  Skipped: 0  Warnings: 0
--表明导入记录4条，删除0条，跳过0条，警告0条

4、验证导入结果

mysql> select * from xifenfei.import;
+------+------+------+
| a    | b    | c    |
+------+------+------+
| a    | b    | c    |
| e    | f    | g    |
| i    | j    | k    |
| x    | f    | f    |
+------+------+------+
4 rows in set (0.00 sec)

四、mysqlimport使用补充说明
1、文件名的第一个字符串需要和数据库中的表名一致(如:这里的import.data对应的表名就是import)
2、直接通过字符串指定数据库名称(如:这里的xifenfei)
3、行/列分隔符都使用双引号括起来(可能还有其他方案，但是这样方便)

1、修改配置文件
read-only=1（主库）
#read-only=1（备库）

2、查询从库状态
mysql> show processlist ;
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
| 1 | root | localhost | ecp | Query | 0 | NULL | show processlist |
| 4 | system user | | NULL | Connect | 2 | Waiting for master to send event | NULL |
| 5 | system user | | NULL | Connect | 2 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
3 rows in set (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 107
Relay_Log_File: replicate.000007
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 549
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
1 row in set (0.00 sec)

3、查询主库状态
mysql> show processlist;
+—-+——+——————-+——+————-+——+———————————————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+——————-+——+————-+——+———————————————————————–+——————+
| 1 | root | localhost | ecp | Query | 0 | NULL | show processlist |
| 2 | repl | 192.168.1.4:17948 | NULL | Binlog Dump | 6 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
+—-+——+——————-+——+————-+——+———————————————————————–+——————+
2 rows in set (0.00 sec)

mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

4、从库操作
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW PROCESSLIST;
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
| 1 | root | localhost | ecp | Query | 0 | NULL | SHOW PROCESSLIST |
| 5 | system user | | NULL | Connect | 256 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
2 rows in set (0.00 sec)

确保状态为：has read all relay log

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 107
Relay_Log_File: replicate.000007
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 549
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
1 row in set (0.00 sec)

5、查询主库状态
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

6、从库变主库
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> RESET MASTER;
Query OK, 0 rows affected (0.02 sec)

mysql> RESET SLAVE;
Query OK, 0 rows affected (0.03 sec)

mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

7、主库变从库
mysql> RESET MASTER;
Query OK, 0 rows affected (0.06 sec)

mysql> RESET SLAVE;
Query OK, 0 rows affected (0.03 sec)

mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.4′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’xifenfei’,
-> MASTER_LOG_FILE=’mysql-bin.000001′,
-> MASTER_LOG_POS=107;
Query OK, 0 rows affected (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

8、重启主和从库
[root@localhost mysql]# service mysql restart
Shutting down MySQL….[ OK ]
Starting MySQL…………….[ OK ]

9、检查主从是否都正常
主库
SHOW PROCESSLIST;
show master status \G

从库
SHOW PROCESSLIST;
start slave;
show slave status \G

如果有错误，根据错误提示，解决问题
创建主从复制，请见使用xtrabackup 配置主从服务器