MySQL Reference Architectures for Massively Scalable Web Infrastructure

I Found this pdf informatics,

It list out definition of small, Medium, Large, Extra-large web architecture from MySQL prospective and give example of architecture as well

MySQL Reference Architectures for Massively Scalable Web Infrastructure

 

Release date: March 2013

 

We need register one oracle login to download the articles

MYSQL : Error message file 'usr/local/mysql/share/english/errmsg.sys'

MYSQL Lab setting error :
MYSQL : Error message file 'usr/local/mysql/share/english/errmsg.sys'

I setting MySQL lab to test replication.
Binary file place in /MySQL/master and /MySQL slave

few parameter in my.cnf been change
port=3306
socket=/tmp/mysqlmaster.sock
datadir=/MySQL/master/data

when I start master server, it hit error 

Google and found similar problem in the blog.
Toaster Gremlin
In the blog, the problem been solve with replace errmsg.sys with the latest MySQL version in /local/usr/MySQL/share/English folder

 

It give me hints!

Early I install mysqlcluster in /local/usr/MySQL folder for MySQL cluster lab setup.
When I start install MYSQL 5.5.30 into /MySQL/master folder, my.cnf didn't mention my basedir.

I suspect it get share information from /local/user/MySQL folder which is the default MySQL installation folder.

Table 2.5. MySQL Installation Layout for Generic Unix/Linux Binary Package

 

I add basedir parameter in my.cnf under mysqld session where /MySQL/master is MySQL binary file location

basedir=/MySQL/master

I start mysqld again with successful

 

Problem solve!



Renew ip in Sun Solaris (Virtual Server)

I attended MYSQL Cluster  training from Oracle and using Sun Solaris in Hyper-v for Lab purpose.

When I brought my computer to training center, I needed renew my IP so I can't connect to Sun Solaris in Hyper-v with Tightvnc viewer (I use this to solve Mouse trap problem in Sun Solaris at Hyper-V)

In window environment, I always run this
IPCONFIG /renew

In *inux version, I don't know how and always restart Virtual server to get the latest IP.

Do some search and come this
list ip of the network in my virtual pc
/sbin/ifconfig -a

net0 is my ip.

I run the below
 ifconfig net0 dhcp status
ifconfig net0 dhcp release
ifconfig net0 dhcp status

and then

ifconfig net0 dhcp primary
ifconfig net0 dhcp status
The IP is refresh.

I get the information from here and it work in my Virtual Server.

MYSQL Post Installation - configuration change

MYSQL Post Installation - configuration change

What to tune in MySQL Server after installation

Replication - Relay log corrupt

My replication stop
Check with
show slave status\G

Last_Error :Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Check error log
100420 20:28:02 [ERROR] Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000078' position 1112628
100420 21:26:58 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000078' at position 1112628, relay log '/usr/local/mysql/relay-bin/relay.000714' position: 002082
100420 21:27:00 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 7195, event_type: 16
100420 21:27:00 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
100420 21:27:00 [ERROR] Slave: Could not parse relay log event entry. The possible reasons are:
the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log),
the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log),
a network problem, or a bug in the master's or slave's MySQL code.
If you want to check the master's binary log or slave's relay log,
you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
Error_code: 0



check relay log '/usr/local/mysql/relay-bin/relay.000714'
log disappear. lateset one is relay.000722


Reset relay log

Check the below information with
show slave status\G

Relay_Master_Log_File: mysql-bin.000078
Exec_Master_Log_Pos: 1996668

The command should be as below
CHANGE master TO master_log_file=Relay_Master_Log_File,master_log_pos=Exec_Master_Log_Pos


So I run this
CHANGE master TO master_log_file=mysql-bin.000078,master_log_pos=1996668


The relay_log in /usr/local/mysql/relay-bin want delete, and new relay log is populate
MYSQL Replication is resume


The above solution work fine for my server

Reference :
http://sql.dzone.com/news/troubleshooting-relay-log-corr
http://www.mysqlperformanceblog.com/2008/08/02/troubleshooting-relay-log-corruption-in-mysql/

Grant permission - Checking

If we want to know what permissions have grant to the user,
we can use the below command :

mysql>show grants for 'root';
Grants for Root@%
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION

Reference :
http://dev.mysql.com/doc/refman/4.1/en/show-grants.html

if we want to know what user been grant to table, we can use the below method
mysql> select * mysql.tables_priv where table_name='mytable';


+------+--------+---------------+---------------------+----------------+---------------------+---------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+------+--------+---------------+---------------------+----------------+---------------------+---------------+-------------+
| % | testdb | test_user | mytable | root@127.0.0.1 | 2009-01-08 18:38:14 | Select,Delete | |
| % | testdb | test_user2 | mytable | | 2010-03-23 16:09:00 | Select | |
+------+--------+---------------+---------------------+----------------+---------------------+---------------+-------------+

Innodb table can't create

I want to recreate mysql database
- Stop mysql server, delete all data file and
- Recreate mysql database
sudo scripts/mysql_install_db --user=mysql --datadir=/data
- Restart mysql database
sudo bin/mysqld_safe --user=mysql --default-character-set=utf8
- The ibdata1, ibdata2 recreate and mysql start
- I create table in innodb, but table appear in myisam
CREATE TABLE `staff` (
`staff_id` int(10) unsigned NOT NULL auto_increment,
`staff_name` varchar(300) NOT NULL,
PRIMARY KEY (`staff_id`)
) ENGINE=innodb DEFAULT CHARSET=utf8 ;

it become

CREATE TABLE `staff` (
`staff_id` int(10) unsigned NOT NULL auto_increment,
`staff_name` varchar(300) NOT NULL,
PRIMARY KEY (`staff_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

- Run show engines in mysql, and it show
InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys

- Check mysql error file
InnoDB: Error: all log files must be created at the same time.
InnoDB: All log files must be created also in database creation.
InnoDB: If you want bigger or smaller log files, shut down the
InnoDB: database and make sure there were no errors in shutdown.
InnoDB: Then delete the existing log files. Edit the .cnf file
InnoDB: and start the database again.

Finding:
- When delete file, I forgot delete ib_logfile0 and ib_logfile1

Solution
- Stop mysqlserver
mysqladmin -uroot -ppassword shutdown
- Delete ibdata1, ibdata2, ib_logfile0,ib_logfile1
- restart mysqlserver again
sudo bin/mysqld_safe --user=mysql --default-character-set=utf8
- ibdata1, ibdata2, ib_logfile0,ib_logfile1 rebuild
- Create innodb table success
- Run show engines in mysql, and it show
show engines
- It return
InnoDB | Yes | Supports transactions, row-level locking, and foreign keys