Make a copy of mysql table
One SQL to make a copy of existing MySQL table ( table structure and records )
1 | CREATE TABLE clone_table SELECT * FROM original_table WHERE 1 |
Posts Tagged ‘MySQL’
21
Oct
2009
Oct
2009
29
Nov
2008
Nov
2008
Backup MySQL databases to remote server using mysql-zrm
Recently I use mysql-zrm to backup a large databases from a client server. I can backup all databases or a set of database or maybe some of tables with one line linux command. Pretty easy to use.
You may imagine that mysql-zrm is a strong management tool of mysql, mysqldump and mysqlhotcopy. The key benifits for me are : I can backup all databases at once with my databases are automatically created if a new client comes; mysql-zrm is able to save compressed data to different folder, so I can keep saved data up to seven days (you may do it one month or maybe one year); It also has a scheduler to do your task every certain time you want.
Installation:
1. login to your backup server
2. Download rpm from http://www.zmanda.com/download-zrm.php
3. If you don’t have Perl installed, run ” yum install perl ”
4. run ” rpm -ivh MySQL-zrm-2.0-1.noarch.rpm ”
5. “ vi /etc/mysql-zrm/mysql-zrm.conf ” update following parameters in file
backup-mode=logical [ ideally raw for myisam engine and logical for innodb. but I recommend using logical for default. Because when I use raw for default value, and if there is table with innodb engine exist, zrm will prompt me password for mysql@(server_ip) which have no idea about linux mysql user login ]
destination=/home/mybackup_dir [ specify where backup files will be stored]
Start backup:
mysql-zrm –action backup –host xxx.xxx.xxx.xxx –user aabb –password ccdd –backup-set abcd
where
–host is remote server IP for MySQL
–user is remote MySQL server user login
–password is remote MySQL server user login password
–backup-set is backup folder name
Result:
after running the command, you will get backup file in the path look like this
/home/mybackup_dir/abcd/20081128195236
Restore database to local server:
/usr/bin/mysql-zrm-restore –user=1122 –password=2233 –source-directory=/home/mybackup_dir/abcd/20081128195236
check out your backup server MySQL database. 
04
Mar
2008
Mar
2008
Check Linux Versions – common commands part1
- Check Linux Kernel version:
uname -a
- Check Linux version
cat /proc/version
- Check Redhat based Linux version
cat /etc/redhat-release
- Check PHP version on Linux server
php -v
- Check mysql version on linux server
mysql -V
- Check apache version on linux server
httpd -v
11
Oct
2007
Oct
2007
Install MySQL Community Server 5.0.45 Without Installer on Windows Vista
- Download the zip file from here.
- Unzip it to your d: drive. Here’s what you should get after unzipping: d:\mysql-noinstall-5.0.45-win32. Rename this folder to mysql5045. (d:\mysql5045)
- Open folder d:\mysql5045. Copy my-small.ini and rename the copied file to my.ini. Open my.ini using notepad. Under line “[mysqld]“, enter the following lines:basedir=D:/mysql5045
datadir=D:/mysql5045/data - Run your command prompt as administrator. And enter folder d:\mysql5045\bin
- Type mysqld-nt.exe –install mysql5045 –defaults-file=d:\mysql5045\my.ini
- Type net start mysql5045 in command line. If you see the following, mysql 5045 is installed and running!The mysql5045 service is starting.
The mysql5045 service was started successfully. - IMPORTANT! type mysqladmin -u root password “your-new-password”, to set a password for your root account. The default install will leave no password required for your root account.
