Database Engine for MySQL


Today I come across an article database engine for MySQL. Its interesting and I brief some of them.

  1. What are database engines?
  • Also known as storage engines.
  • Software component of the database management. System that actually stores, retrieves, modifies and deletes data.

2. Why do we need different database engines?

  • Every applications has different requirement.
  • For some applications performance is major issue.
  • For some applications frequent read/write are more important than frequent updates.
  • For some application searching is major issue compared to the read/write/update.

3. Types of database Engines?

InnoDB,MYISAM,BerkleyDB etc., Every database engines has its different pros and cons.

          MYISAM:

  • Designed for database are frequently read not updated.
  • High performance and good for frequent read operations.
  • Locks table while updating.

          InnoDB:

  • Designed for database those are frequently updated not read.
  • InnoDB is transaction-safe.
  • Data Integrity is maintained throughout the entire query process.
  • Allow multiple updates on single table. Locks only row not table.

4. When to use what database engines?

  • More updates less read
  • More performance
  • Bank
  • Social networking website.

 

This storage engine is used for tables and not to database.

To set the default storage engine according to our requirement, go to /etc/mysql/my.cnf; (the path may vary to different distros. Here I am using Ubuntu)

Add a new line in [mysqld]

Default-storage-engine = innodb

Save the my.cnf file and restart MySQL service.

In Mysql replication Slave_IO_Running:No


Root cause: The error occur due to the master log file does not match in the slave when you query in mysql “show slave status\G” .

Solution:

a. Check the master log file in mysql master by

mysql> show master status;

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000154 | 107 | | |

+——————+———-+————–+——————+

1 row in set (0.00 sec)

 

mysql>

b. Compare the log file in slave by running the command “show slave status\G” and try to set the same log file in slave mysql server.

c. Go to slave mysql server and run the below command.

mysql>STOP SLAVE;

mysql>CHANGE MASTER TO MASTER_LOG_POS = 0;

mysql>CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000154';

mysql>START SLAVE;

 

This solves the problem…

 

Mysql Error: ERROR 2005 (HY000): Unknown MySQL server host ‘domainName:3306’ (11004)


Root cause of the problem:

When you connect the remote mysql server either through IP address or host name, you will get an error as mentioned in the subject.This is due to my.cnf bind address is not correctly entered.

Solution:

Give appropriate host IP address or Host name in the bind address in my.cnf in the remote server.

 

How To Set Up Database Replication In MySQL


1. Configure The Master

a. First we have to edit /etc/mysql/my.cnf. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out the line

#skip-networking
bind-address = X.X.X.X host IP instead the default IP (127.0.0.1)

b. Furthermore we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database test, so we put the following lines into /etc/mysql/my.cnf:
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=test
server-id=1

c. Then restart Mysql service and login to the mysql prompt, give the following permission.

mysql>GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘<some_password>’;

mysql>FLUSH PRIVILEGES;

 

d.To identify mysql log file in master type the below command

 

mysql> SHOW MASTER STATUS;

 

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000155 | 198 | test | |

+——————+———-+————–+——————+

1 row in set (0.00 sec)

 

mysql>

 

e. Then quit.

 

 

2. Configure The Slave

 

a. create a database name test in mysql.

 

b. Go to /etc/mysql/my.cnf in the slave server and do the following changes.

Comment the line and type the host ip address instead the default.

#skip-networking
bind-address = X.X.X.X host IP instead the default IP (127.0.0.1)

Also enable or uncomment the below line (server-id)in my.cnf

server-id=2 (here we should mention 2 instead the default 1, since the master is configured as 1)
master-host=192.168.0.100 (add a new entry)

c. Save the my.cnf file and restart mysql.

d. Go to mysql prompt in slave and type the command 
   mysql>show slave status\G;

The Slave_IO_Running: YES ; Slave_SQL_Running: Yes should be in the status yes.
If the IO running No; then the log file in master doesnot match the slave. To over come this issue

mysql>STOP SLAVE;

mysql>CHANGE MASTER TOMASTER_LOG_POS = 0;

mysql>CHANGE MASTER TOMASTER_LOG_FILE = 'required bin file';

mysql>START SLAVE;

Now test the replication in mysql test DB by manipulating creating or inserting or deleting any table in the test DB.
It should work...






Database Startup fails with error ORA-16038,ORA-19809, ORA-00312


I am not a DBA expert, but I had an opportunity to work with DBA for the below problem raised in Oracle database. When I try to backup oracle server my backup fails. When I check with my DBA I come across this below error. I find the solution from the url

http://arjudba.blogspot.in/2008/07/database-startup-fails-with-error-ora.html

Error Description:

Whenever you try to startup the database it fails with error ORA-16038,ORA-19809, ORA-00312.

SQL> startup

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 2019288 bytes

Variable Size 117440552 bytes

Database Buffers 41943040 bytes

Redo Buffers 6369280 bytes

Database mounted.

ORA-16038: log 3 sequence# 572 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 3 thread

1: ‘/oradata2/data1/dbase/redo03.log’

Or in mount stage whenever you try to open the database it fails with error ORA-16014, ORA-00312.

Root Cause: 

There was an attempt to archive the online log 3 but it could not archive the online log in the available archived log destination. The most common of happening the error is the archive log destination if full. You have flash recovery area configured and rman retention policy is failed to delete any archived or incremental backups and so can’t archive new online log.

Solution A:(Enough space on the drive)

1)One more check the alert log. (Not needed though) An extra check you can do in alert log which is in

background_dump_dest/alert_$ORACLE_SID.log

SQL> show parameter background_dump_dest NAME TYPE VALUE

———————————— ———– ———————-

——–

background_dump_dest 

string /oracle/app/oracle/product/10.2.0/db_1/admin/dbase/bdump

$less /oracle/app/oracle/product/10.2.0/db_1/admin/dbase/bdump/alert_dbase.log

 You may see the same entry is in the alert log.

 2)Check the archive destination.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 572

Next log sequence to archive 572

Current log sequence 580

So archived log destination is DB_RECOVERY_FILE_DEST.You can see the exact destination in OS by,

 

SQL> show parameter db_recover NAME TYPE VALUE

———————————— ———– ———————-

——–

db_recovery_file_dest

string /oradata2/flash_recovery_area db_recovery_file_dest_size big integer 10G

3) Increase the value of db_recovery_file_dest_size

As archive destination is full so increase the size.

 

SQL> alter system set db_recovery_file_dest_size=20G;

System altered.

4) Open the database now.

SQL> alter database open;

Database altered.

Solution B: Have not enough space on the drive. If you have not enough space in your disk and you have recent backup of your database and archive log is not needed then you can issue

 $rman target /

RMAN>DELETE ARCHIVELOG UNTIL

TIME ‘SYSDATE-2’;

 Solution C: Have not any backup. If you have not any recent backup then backup database to

another location and delete archivelogs. To do this,

$rman target /

RMAN>backup format ‘/oradata2/%U’ archivelog all delete input database;

 

Solution D: Have recent backup and only need archivelog. In this case back up the archive log to another location and delete archive log from flash recovery area. You can do this by,

 $rman target /

 RMAN> backup format ‘/oradata2/%U’ archivelog all delete input;