Category Archives: database

Ways to auto backup MySQL database

1. AutoMySQLBackup

AutoMySQLBackup with a basic configuration will create Daily, Weekly and Monthly backups of one or more of your MySQL databases from one or more of your MySQL servers.

Other Features include:
– Email notification of backups
– Backup Compression and Encryption
– Configurable backup rotation
– Incremental database backups

2. Exabytes off-site backup RM199/mo for 50GB.

3. Handy Backup

4. Percona XtraBackup (incremental backup)

5. Explaining on AutoMySQLBackup and MySQL Workbench

Append 0’s in MySQL

In MySQL there is function to append character to string.

LPAD for left padding
LPAD(str,len,padstr)

RPAD for right padding

Example of usage

1
2
3
UPDATE sometable
SET FIELD = LPAD (FIELD,'5','00')
WHERE fieldid = 1

InnoDB vs MyISAM

Set the DB to InnoDB instead of MyISAM

MYISAM:

MYISAM supports Table-level Locking
MyISAM designed for need of speed
MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
MYISAM supports fulltext search
You can use MyISAM, if the table is more static with lots of select and less update and delete.
INNODB:

InnoDB supports Row-level Locking
InnoDB designed for maximum performance when processing high volume of data
InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
InnoDB stores its tables and indexes in a tablespace
InnoDB supports transaction. You can commit and rollback with InnoDB

MAMP MySQL cannot access

Some important notes for root password change for MAMP

To change root password to empty/blank again, open terminal and type
/Applications/MAMP/Library/bin/mysqladmin -u root -p’root’ password ”
(root is the original password for mysql in mamp)

MAMP page
In: /Applications/MAMP/bin/mamp/index.php
Change to: $link = @mysql_connect(‘:/Applications/MAMP/tmp/mysql/mysql.sock’, ‘root’, ‘newpassword’);

PHPMyAdmin
In: /Applications/MAMP/bin/phpMyAdmin/config.inc.php
Change to: $cfg[‘Servers’][$i][‘password’] = root

Reset root password for MySQL on windows

If you have problem accessing your MySQL database with this error
ERROR 1045: Access denied for user: ‘root@localhost’ (Using password: NO)

You can try reset your root password with the following steps:

1. Create a txt file with this content

UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
FLUSH PRIVILEGES;

2. Save the file as mysql-init.txt in c:\ (or any directory you want)

3. Stop MySQL service

4. Start MySQL service in command prompt with this command line

>c:\mysql\bin\mysqld --init-file=c:\\mysql-init.txt

5. Remove the txt file in created in step 2

Note: You can/need to specify correct path for the file and mysql directory for step 2 and 4.

MySQL database auto backup on windows

To backup mysql database (all databases) can use this batch file

Then you can compile the .bat file to become .exe file so that the password is hidden.

Download bat to exe converter from CNET download

Lastly, you can include the exe file to run in task scheduler

You can have option to upload the file via FTP to other server

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
:: Start FTP files to remote server
:: Create the temporary FTP script file
> script.ftp ECHO ftpusername
>>script.ftp ECHO ftppassword
>>script.ftp ECHO lcd /
>>script.ftp ECHO lcd /MySQLBackups/backupfiles
>>script.ftp ECHO binary
>>script.ftp ECHO prompt n
>>script.ftp ECHO put FullBackup.%backupdate%.zip
>>script.ftp ECHO bye
:: Use the temporary script for unattended FTP
:: Note: depending on your OS version you may have to add a '-n' switch
FTP -v -s:script.ftp ftphostcom.com
:: Overwrite the temporary file before deleting it
TYPE NUL >script.ftp
DEL script.ftp
:: DONE FTP backup file to remote server