Topic: MySQL database operations

Http://www.javaeye.com/topic/251307 Author: touya

The project was released yesterday, and data migration was required. The functions to be implemented were abnormal and the time was very short. Basically, the work could be completed quickly and accurately by making full-body solutions, during this period, we found that many tips are used in tandem, and the effect is indeed very good.

Weapons:

1 mysqldump + MySQL command => data import and export, Backup Recovery

2 Perl Command Line => in many cases, you have an idea that you can use only one sentence to help you implement it, saving you a lot of coding effort.CodeTrouble

3 shell command => combination of boxing, which can be used together by any single command, requires some experience.

4. The powerful Editor supports ultraedit, emedit, etc. => ultraedit is the most professional, but does not support Character Set conversion. emedit supports Character Set conversion, and many plug-ins are available in the latest version, it is a common operation. For Web developers, it is often necessary to transfer the code in gb2312, utf8, or other languages such as EUC-JP and shift-JIS. emedit is indeed the first choice, in particular, emedit's keyboard recording and playing functions are also praised.

Common combinations:

1. Data Migration:

Mysqldump -- opt-t-h locahost-P 23236-P mydb table-W "id> 27000"> table. SQL

> Enter the password

OK. The insert statement for all data columns with an ID greater than 27000 in the Table in mydb is saved to table. SQL. -H,-P, and-P are not much said. -- OPT is the most common combination option of mysqldump and can be considered as an acceleration statement;-T is very useful, it indicates -- no-create-Info, that is, you do not need to create a table statement, you only need data (insert statement);-W is the where condition, it is also very useful, you can choose to export data. Other common options:-D: only the table creation statement is required, and the insert statement is not required.-C: add the column name (field) to each insert statement. By default, the insert statement is "insert into 'table' values (1), (2), (3);", and after-C is added, it becomes "insert into table (ID) values (1), (2), (3 );". What is the purpose? I will understand it later.

If there is a large amount of data in the table, the export insert statement is very long (by default, all data in a table is in an insert statement without line breaks ), to view this file, it is often caused to crash, whether in Linux VI, less view, or Windows emedit Editor, because they all load data in the unit of behavior, if a row of data is too large, the memory usage will be too large. What should I do?

You find that you only need to search for ") in emedit (" this string, replace it with "), \ n (", that is, add a line feed, as a result, we found that when the data volume reaches, emedit is replaced one by one like shuyang. After the change, you have all gone to bed !!

What should we do? Of course you know that it's easy to write a script, but it takes a lot of time and you still need to test it. Is there a simple way? Well, it's Perl's turn to play with a single command line:

Perl-I. Bak-PE's/\), \ (/\), \ n \ (/G' table. SQL

OK, the replacement is complete, and a backup file table. SQL. Bak is generated. If you find that something is wrong, the effect is incorrect, and the result is saved :)

Of course, if you use powerful editing tools such as SED and awk, this is also a small case, so I will not talk about it here.

2. Data Import

After the exported data is processed, it is necessary to import it to the target database. There is a table with many fields. The primary key is ID, which imports the same table of the other database, but the ID does not need to be inserted directly, instead, it is recommended that the Id be automatically generated after the last data entry in the target data table to avoid primary key conflicts. How can this problem be solved?

In this case, you can use the-C option mentioned above. After adding-C, the exported data has a field column. You only need to replace all the ID columns with null values :)

In this replacement process, emedit or the Perl Command Line mentioned above can be used, but the speed is different.

3. The old data is imported into a new table and a new continuous ID is generated, but it is completely different from the old data, currently, the IDs of old data are used as foreign keys in several other tables. You need to change them to new IDs. For example:

Table in old data. id = 10010. There is a table table2.table = 10010, which is a foreign key relationship. Change table2.table to the ID automatically generated in the new data table, for example, 6041. currently, only the list of old IDs is in the hand, and you know that IDs in the inserted new table are auto-incrementing. Each time you add 1, starting from 6041, how can you quickly modify Table2, what about the corresponding foreign key in Table 3?

One file:

10010

10201

11301

11499

......

To replace it:

Update Table2 set table = 6041 where id = 10010;

Update Table2 set table = 6042 where id = 10201;

Update Table2 set table = 6043 where id = 11301;

Update Table2 set table = 6044 where id = 11499;

......

We can combine the tools at hand in this way:

Perl-I. bak-PE 'in in {$ x = 6041} s/^ (\ D +)/update Table2 set table = $ X where id = $1 ;/; $ X ++ 'file.txt

OK.

The special feature of this example is actually the usage of begin in the Perl Command Line, which includes begin and of course end. Check the document for details :)

4. Recording keyboard operations and playing in emedit are also very common. After all, writing regular expressions is a little tricky. Although the tool is slower, it is much faster to think about. One thing happened today, we need to convert BMP images in a directory on the server into JPG format. We also need to use combination boxing, where the recording and playing functions of emedit are used.

It is known that there are n multiple BMP images in a directory. You can use the convert command to convert the format (install the image: magick module)

What should I do?

Find./-name "*. BMP"> BMP.

Get the file list first.

Open the list in emedit, with a row of file names.

What you want to do is:

./Dir1/dir2/file1.bmp

./Dir1/dir2/file2.bmp

./Dir1/dir2/file3.bmp

To:

Convert./dir1/dir2/file1.bmp./dir1/dir2/file1.jpg

Convert./dir1/dir2/file2.bmp./dir1/dir2/file2.jpg

Convert./dir1/dir2/file3.bmp./dir1/dir2/file3.jpg

This is a very regular action. At the beginning of the line, press SHIFT + end (select), CTRL + C (copy), end (to the end of the line), and spaces, CTRL + V (paste), backspace three times, input JPG, home to the beginning of the line, enter convert space, next line, home to the beginning of the line

You only need to record the Operation Sequence and perform the same operation on each line. In this case, you can use the recording + playback function of emedit. After the recording is complete, press the F4 shortcut key to record the operation sequence ~~ Yeah ~~

Delete the source image after running:

In shell:

Find./-name "*. BMP" | xargs-N1 Rm-F

(Find the list of BMP image file names in the current directory and send them to RM-f one by one. delete them with caution)

5. database recovery (restoring the database from the bin-log is also a very important technique). When a misoperation occurs or the DB server accidentally loses data, we often need to check the bin-log to try to restore the data. Next time, let's talk about it. tired ......

Oh, it seems that the database operation is not the only thing you need to do. You are welcome to make a picture.