My MySQL replication slave stopped replicating the master. Checked the log file it has this error:
Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master (server_errno=1236)
Got fatal error 1236: ‘log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master’ from master when reading data from binary log
Naturally I started increasing the max_allowed_packet in my.cnf. No help. So I looked up the web and found this bug documented. Bingo I thought…oh wait, the bug was discovered a few years back and my version of MySQL should have included the fix already. Ok back to square one.
Just to poke around I ran “show master status” on the master server and compare bin file name and position with what’s in the logs on slave server. The files do not match. It may or may not cause the error 1236 but it’s definitely not right. So I went ahead ran the following command to re-sync:
On the slave:
mysql -u root -pxxxxx
SLAVE STOP
LOAD DATA FROM MASTER
CHANGE MASTER TO MASTER_LOG_FILE=’the-right-bin.xxx’, MASTER_LOG_POS=the-right-position;
SLAVE START
Now step two to load data from mater created a lot of error logs since my slave DB had had data so I really should have clean up the DB before run the commands.
So the replication is back to work now. Not sure what had really caused error 1236. Get my fingers crossed.
Amazon has released its Relational Database service today. It has the full capability of MySQL and the service lives in the Cloud, as its other services like S3 or SQS, etc do.
Also similar to other services, it is charged by usage.
Why it is a great news? A couple of things came to my mind when I heard it.
RDS can be your single DB server. If you are a web developer who manages a small/medium site on shared hosting server you won’t have the luxury to build a standalone DB server and you’ll likely end up with Apache and MySQL running on the same box, and you know it is a pain in the butt when moving a site from one host to another (transferring files, re-establish DB, and probably making code changes to point to the new DB). With RDS, you can simply create your DB instance in the cloud and skip moving DB when you need to move your hosting service. This may sound only benefit your hosting company since one less use is competing for the resource, but you definitely get the advantage of flexibility and probably some increment on reliability.
However, moving DB to cloud will increase network latency, and as a developer I have my share of concern on the Cloud-based services. It’s out there and I have very little control of. We have had a few times of S3 going offline in the past and it’s not fun for those services or sites whose architectural diagram live inside the cloud service itself. So for a large site owner with a “mission critical” database, I would recommend using RDS to cut down the cost of setting up DB mirrors. For instance, we often using a second MySQL DB instance to sync with the master; and RDS can replace the slave in this case. It’ll be useful for failover, backup and real time restore, and a little bit of downtime is probably okay. The biggest benefit is that we can save a lot by eliminating the cost of server, software management and system admin work.
It’s certainly a bad news for service like FathomDB, which is a cloud relational DB service built based on Amazon’s EC2. This probably makes a good argument against making a long term commitment on building services which rely on platforms that in other people’s control. When a nice Facebook app get popular, who’s to stop Facebook to build the similar one for their own as the market has been proven? Same goes to iPhone apps. Make some quick bucks and move on is the way to go.
Any other ideas of using RDS? Let me know. Cloud is certainly making things cheaper for us web builders.
VPS with 726M ram
—- starting my.cnf —
[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log
[mysqld]
skip-networking
skip-innodb
skip-locking
log-slow-queries
long_query_time=3
#log-bin=fmrbinlog
#binlog-do-db=fmr_fmr
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 16M
thread_concurrency = 2
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
—- ending my.cnf —-Â
Note: key_buffer and table_cache are shared by all connections; read_buffer is for each thread.
further adjusted key_buffer down to 128M based on the tuning-primer.sh recommendation.
show some settings:
mysql>show variables;
mysql>show status;
Almost all my sites are database driven using MySQL. I use phpMyAdmin for data manipulation most of the time. But I also need a way to keep track of the design and quickly analyze which tables to touch in case there is need for data repair. I though about drawing them on paper but it can be hard to put down 30 tables.
Recently I tried a couple of free product and the goal is to use the reverse engineering functionality to create a visual table schema so I can refer to from time to time.
First I tried to use the MySQL GUI tool from the mysql.com.
The package includes some nice tools but I was only interested in the MySQLWorkbench. Once connected to the database, it can reverse engineer the table design and present it on a nice graphic page. I re-arranged the tables and decided to print it out. And here is when the problem came. No matter how I tried the tool won't print for me. It seemed to hang on my printing command the started to "not responding". After spending a couple of hours I had to give up on my nicely arranged table schema and move on.
After some research I discovered DBDesigner.
Besides other nice features it also has the reverse engineering feature. And most importantly, it works from top to bottom. After some work I have my table schema printed out and stick to the wall now.
Both tools can be downloaded as zip file and extracted directly to your hard drive without an "installation" process. And both tools offer a lot of nice features and what I was doing was just scratching the surface. Since MySQLWorkbench is still an alpha product I can't complain too much. And hat off to DBDesigner, it really makes my work easier.
The regular query log:
The MYSQL query can be logged by starting mysqld with –log[=file]
or adding this line in my.cnf file:
log=/var/log/mysql.log (/var/log/mysql.log is the actual file path)
Since the query log will record every query happening on every connection it is more or less a overhead for the mysql for server.
The logrotate utility can be used to manage the log files generated in this way.
The binary log
The binary log records every updates happened in the database. As the name suggests, it is stored in a binary form . The parameters that are related to this setting:
log-bin[=binary-log-file-name]
binlog-ignore-db=the-db-to-ignore
binlog-do-db=the-db-updates-need-logging
The log file(s) will be managed by the MYSQL server and each file will be appended with an index as the new file gets rolled in. The files look something like this: mydb-bin.000001, mydb-bin.000002,…
Since it's in binary form, we can't view them directly. This command comes in for viewing the binary log:
% mysqlbinlog mydb-bin.001
The error log
The error log stores all the errors (obviously). In a Linux system, it can be found under /var/lib/mysql, which is the default installation path for MYSQL. Ideally you should only see messages like "mysqld started" and "mysqld ended"… type of messages.
The slow log
This is a neat feature of MYSQL. Basically this log stores the "slow" queries that have run on the server. The default one will be servername-slow.log. To define "slow", you can add this to the my.cnf:
long-query-time=1 (comment: one second is too slow!)
MYSQL reference manual.
Create a new user:
grant all on mydb.* to myuser@localhost identified by 'mypass';
show grants for ‘username’@'localhost’
revoke all privileges on db_name.* from ‘username’@'localhost’
drop user ‘username’@'localhost’