Sunday, February 13, 2005

Commands I use, but often forget

Importing (Restoring) Many MySQLDump Text Files:

~ from a user (paraphrased): "If we have 100+ mysqldump *.txt files, how can we easily import them all, in order to restore a database, without typing each file name in by hand? (i.e. from the shell prompt, piped to the mysql monitor:
mysql mydb <>). Wouldn't it be better for the script to write all the tables to one text sql file?"

=> I suppose the script could be rewritten, but it's more flexible the way it is, considering that one can use the Linux shell prompt to do the same thing. Assuming that the database name is 'test', and that the files have the extension of '.txt', you can use this command:

find . -name "*.txt" -print | xargs -t --replace cat {} | mysql test

That parses through all the files and reads the contents using 'cat' and then pipes the contents one by one to the mysql monitor with the database 'test'. The '-t' shows you the file names as it works, so you're not staring at a blank screen. If you change the '-t' to a '-p', it will prompt you at each file. Try it on a test database first, to be safe :-) Note: I think the xargs version above is better than:

cat *.txt | mysql test

... because of the limitations of the command line size, assuming one has many files. Also, the xargs version shows you the filenames as they process. Of course, there are probably many other ways to accomplish the above, given the eclectic nature of Unix.

find . -name "*.sql" -print | xargs -t --replace cat {} | /usr/local/mysql/bin/mysql --password -D pm

Secure copy file from one host to RemoteHost:

scp $FileName $UserName@$RemoteHost:/path/on/remote/host/$FileName

Connecting to MySQL 4.1 with Old Libraries

If you need to connect to a MySQL 4.1 Database using an old mysql library, you need to change the password for the user account that you want to connect with, so that it uses an old password. The new version of the database uses a different password algorithm which is much longer then the old version, which causes problems with the old library. To fix this you have to set the user password with the Old_password( ) function.

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');

Ken Cochrane