Judul : Restoring Mysql Databases
link : Restoring Mysql Databases
Restoring Mysql Databases
(How to restore mysqldump �all-databases backup ?)
[mysql@INVIRH54DB3 ~]$ mysql -u root -p < /tmp/alldbs55.sql
Enter password:mysql
Or
Mysql �u root �ppassword@<alldatabases.sql
Restore asingledatabase:
In this example, to restore the newdb database, execute mysql with < as shown below. When you are restoring the dumpfilename.sql on a remote database, make sure to create the newdb database before you can perform the restore
# mysql -u root -p[root_password] [database_name] <dumpfilename.sql
Import data in MySQL
Create an employee table and employee.txt data file.
For the examples, let us create a very simple employee table with three columns�employee number, employee name andjob.
(1)Mysql -u root -ptmppassword
Mysql> use test
Database changed
mysql> create table employee (empnoint,enamevarchar(15),job varchar(10));
Database changed
mysql> create table employee (empnoint,enamevarchar(15),job varchar(10));
(2)Create a test datafile employee.txt with fields delimited by tab as shown below.
Cat employee.txt
100 John Doe DBA
200 John Smith Sysadmin
300 Raj Patel Developer
200 John Smith Sysadmin
300 Raj Patel Developer
(3)Upload tab delimited datafile to MySQL table
Use mysqlimport to import the employee.txt datafile to employee table in test database, as shown below:
mysqlimport -u root -ptmppassword --local test employee.txt
test.employee: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
(4)Verify that the records got uploaded successfully.
mysql -u root -ptmppassword
mysql> use test;
mysql> select * from employee;
Note: In mysqlimport, the name of the datafile should match the name of the table. The extension of the datafile can be anything. In the above example, only employee.* datafilecan be used to upload data to employee table. You�ll get the following error message when the filename is not same as tablename:
mysqlimport -u root -ptmppassword --local test emp.txt
mysqlimport: Error: Table 'test.emp' doesn't exist, when using table: emp
[Note: The table name is employee. So, datafile name should be employee.*]
[Note: The table name is employee. So, datafile name should be employee.*]
(5) Import multiple datafiles into multiple MySQL tables:
The following example uploads data from two different datafiles to two different tables. I.e. It uploads employee.txt to employee table and manager.txt to manager table.
mysqlimport -u root -ptmppassword --local test employee.txt manager.txt
(6) Use LOAD DATA LOCAL INFILE to upload data to MySQL tables:
The mysqlimport client is simply a command-line interface to the LOAD DATA LOCAL INFILE SQL statement. Most options to mysqlimport correspond directly to clauses of �load data local infile� syntax. You can perform the same upload explained in example#1 using �load data local infile� instead of mysqlimport as explained below:
mysql -u root -ptmppassword
mysql> use test;
mysql> LOAD DATA LOCAL INFILE '/home/ramesh/employee.txt' INTO TABLE employee
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (empno, ename, job);
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from employee;
+-------+------------+-----------+
| empno | ename | job |
+-------+------------+-----------+
| 100 | John Doe | DBA |
| 200 | John Smith | Sysadmin |
| 300 | Raj Patel | Developer |
+-------+------------+-----------+
3 rows in set (0.00 sec)
� Load a CSV file into a table.
Mysql> LOAD DATA INFILE �/tmp/filename.csv replace into table [tablename] fields terminated by �,� lines terminated by �\n� (field1,field2,field3);
(7)Most frequently used mysqlimportoptions:
The most frequently used mysqlimport options are shown in the example below. Most of these options are self-explanatory.
Compress: Compress all information sent between the client and the server
delete: This option is very handy when you want to empty the table before importing the text file local: Read input files locally from the client host
lock-tables: Lock all tables for writing before processing any text files. This ensures that all tables are synchronized on the server.
1. mysqlimport \
--user=root \
--password=tmppassword \
--columns=empno,ename,job \
--compress \
--delete \
--fields-optionally-enclosed-by='"' \
--fields-terminated-by='\t' \
--fields-escaped-by='' \
--lines-terminated-by='\n' \
--local \
--lock-tables \
--verbose \
test employee.txt
Output of the above mysqlimport command:
--password=tmppassword \
--columns=empno,ename,job \
--compress \
--delete \
--fields-optionally-enclosed-by='"' \
--fields-terminated-by='\t' \
--fields-escaped-by='' \
--lines-terminated-by='\n' \
--local \
--lock-tables \
--verbose \
test employee.txt
Output of the above mysqlimport command:
Connecting to localhost
Selecting database test
Locking tables for write
Deleting the old data from table employee
Loading data from LOCAL file: /home/ramesh/employee.txt into employee
test.employee: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
Disconnecting from localhost
Demikianlah Artikel Restoring Mysql Databases
Sekianlah artikel Restoring Mysql Databases kali ini, mudah-mudahan bisa memberi manfaat untuk anda semua. baiklah, sampai jumpa di postingan artikel lainnya.
Anda sekarang membaca artikel Restoring Mysql Databases dengan alamat link https://site-car-insurance.blogspot.com/2016/07/restoring-mysql-databases.html