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

MYSQL Replication - Features and Issue

Please make sure read Replication Features and Issues before do replication

Few problem i found / concern i raise when I make replication
1 UUID in master different from slave and it do not pop any error message.

This confirm by refer to Replication and System Functions

work around solution example:
SET @my_uuid = UUID();
INSERT INTO t VALUES(@my_uuid);

quote : The USER(), CURRENT_USER(), UUID(), VERSION(), and LOAD_FILE() functions are replicated without change and thus do not work reliably on the slave.

2. Will now()function replicate correctly from master to slave? or it will generate a new date when insert data into slave?

Quote:
For NOW(), the binary log includes the timestamp. This means that the value as returned by the call to this function on the master is replicated to the slave.

Need take note this statement as well
Quote:
This can lead to a possibly unexpected result when replicating between MySQL servers in different time zones. For example, suppose that the master is located in New York, the slave is located in Stockholm, and both servers are using local time.

more refer to this link

This statement make my attention :
1. Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements are not guaranteed, since the order of the rows affected is not defined. Such statements can be replicated correctly only if they also contain an ORDER BY clause.

2. Replication During a Master Crash
Setting sync_binlog=1 in the master my.cnf to prevent slave not to be able to replicate when the master comes back up from crash
more detail read here

3. query with nondeterministic
It is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is nondeterministic
Examples of nondeterministic statements include DELETE or UPDATE statements that use LIMIT with no ORDER BY clause
Reference : Replication and the Query Optimizer

Repair MYISAM table

1. online
Check table tablename
repair table tablename

2. offline
- Check all myisam table in particular database
shell> myisamchk *.MYI

- Check all myisma table in all database
shell> myisamchk /path/to/datadir/*/*.MYI

- Check and repair all myisam table
If you want to check all MyISAM tables and repair any that are corrupted, you can use the following command:

shell> myisamchk --silent --force --fast --update-state \
--key_buffer_size=64M --sort_buffer_size=64M \
--read_buffer_size=1M --write_buffer_size=1M \
/path/to/datadir/*/*.MYI


Reference
myisamchk — MyISAM Table-Maintenance Utility
Checking Tables with mysqlcheck and myisamchk
How to Repair Tables

"MySQL server has gone away" using mysqlhotcopy

"MySQL server has gone away" using mysqlhotcopy

I add the below statement in my.cnf

[mysqlhotcopy]
interactive-timeout

and mysqlhotcopy successful

show processlist and kill thread

If user not root user and no grant necessary permission,
They only allow seeing their thread when they issue "show processlist"

In order to see all threads in the server, they need "process privilege"
Logon as root and issue the below statement:
grant process on *.* to 'someone'@'%';
flush privileges;

Logon as “someone and issue the below statement:
show processlist;
All threads in mysql server will show.

We try kill one of the thread which no belong to "someone" with issue the below statement:
kill 95;
mysql give this error : You are not owner of Thread 95

If we want "someone" to kill other thread

We need to grant "super privileges"
Logon as root and issue the below statement
grant super on *.* to 'someone'@'%';
flush privileges;

Open the new connection, logon as “someone” and issue the below statement:
show processlist;
All threads in the server will be shows.
Issue the below statement to kill thread which no belong to “someone”
kill 95;
Thread Id 95 successful kill.

Notes:
- The PROCESS privilege can be used to view the plain text of currently executing statements, including statements that set or change passwords.

- The SUPER privilege can be used to terminate other sessions or change how the server operates.

- The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes via SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, and allows you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

Conclusion:
If you have the PROCESS privilege, you can see all threads.
If you have the SUPER privilege, you can kill all threads and statements.
Otherwise, you can see and kill only your own threads and statements.
Reference: http://dev.mysql.com/doc/refman/4.1/en/kill.html

Impossible WHERE noticed after reading const tables

When I use explain it show me the below result

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE noticed after reading const tables

mysql optimizer only work with data with match data.
else optimizer can't work

"Impossible WHERE noticed after reading const tables" mean mysql optimizer don't know estimated what index/ or it data should retrieve without data matching

MySQL Magazine - Issue 7

New magazine from the site

Issue 7

Performance Tuning Best Practices for MySQL

A video by Jay Pipes in one of Google’s Techtalk sessions recorded at 2006. A quit old talk but lot of useful information. Total lenght video around 45min. Take you time and go through on it

How to reset Password in mysql

Change you own password
login into mysql

at the "mysql>" prompt, type:
set password = password("yournewpassword");

Change other user password
SET PASSWORD FOR 'bob'@'%' = PASSWORD('yournewpassword')

Reference
12.4.1.6. SET PASSWORD Syntax

Addition information
Friend feedback and say he follow the instruction but change password fail! why ??
SET PASSWORD FOR 'user1'@'%localhost' = PASSWORD('yournewpassword')

We can check from here
login into mysql server
mysql> use mysql;
mysql> select user,host from user;

user | host |
+-------------+----------------------+
| root | mysqlserver
| user1 | localhost
| user2 | %

If we login and root and want change password for user1,
we should run this
SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('yournewpassword')

if we want change password for user2,
we should run this
SET PASSWORD FOR 'user2'@'%' = PASSWORD('yournewpassword')

How to know which MYSQL Version I Using?

How to know which MYSQL Version I using

mysql -e status|grep 'Server version'
or
mysql -u root -p -e status|grep 'Server version'


Windows users:
mysql -e status

MySQL Magazine - Issue 6

New magazine from the site

Issue Six

SQL monitoring

We have MYSQL in production, and want have something like monitor or feedback on SQL status.

In MSSQL, we can do lot thing
Example : Monitor blocking, long query, job status

Google and find

1. mtop :
mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server.

more information

2. Find this script:


#!/bin/sh
# Variables
DBUSER=someone
DBPASS=something
SECONDS=15
# Main loop
while true
do
mysqladmin -u$DBUSER -p$DBPASS processlist |
egrep -vw 'Sleep|processlist|Binlog Dump' |
awk -F'|' '{print $6, $7, $8, $9}'
# First line of vmstat is historical, so take the second
vmstat 1 2 | tail -1
# Sleep for a while
sleep $SECONDS
done

3. MYSQLReport
mysqlreport makes a friendly report of important MySQL status values. mysqlreport transforms the values from SHOW STATUS into an easy-to-read report that provides an in-depth understanding of how well MySQL is running. mysqlreport is a better alternative (and practically the only alternative) to manually interpreting SHOW STATUS.

Anything else?

MySQL Magazine

I found this site which provide free MYSQL magazines
Currently they have 4 downloaded magazine issue

Issue One - Summer 2007
Issue Two - Fall 2007
Issue Three - Winter 2007/2008
Issue Four - Spring 2008

MYSQL Performance Slide

| View | Upload your own

Different way to install MYSQL

Different way to install MYSQL
1. Install through MYSQL RPM
- Download and run rpm rpmfile.
- The program file will install into file predefine by rpm file
- Example : Red Hat Enterprise Linux 4 RPM (x86)

2. Compile mysql from MYSQL source and install
- We can compile feature we need.
- We can predefine target install folder as well.
- Example : Source

3. Install MYSQL binary version
- Download and copy the code into target folder
- Advantage : We can download into folder we specific
- Example : Linux (non RPM packages) or
Linux (non RPM, Intel C/C++ compiled, glibc-2.3)

4. Use Operating System MYSQL compile version
- Most Linux come with mysql compile version.
- MYSQL version and start and using

MYSQL download page http://dev.mysql.com/downloads/mysql/5.0.html

reset MYSQL "root" password

How to reset MYSQL "root" password :

Stop mysqld and restart it with the --skip-grant-tables --user=root options
(Windows users omit the --user=root portion).

Connect to the mysqld server with this command:

shell> mysql -u root
Issue the following statements in the mysql client:

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

How to create two instance mysql in server

My practices
use binary version - Linux (non RPM packages)
tar to particular folder
change the port (3306.2207) and point socket to individual folder