RSS feed

MySQL Server 5.1 Upgrade: #mysql50#

October 29th, 2010 | Posted in Blog, MySQL | No Comments

MySQL introduced encoding for table names that have non-ASCII characters. After a live upgrade from MySQL 5.0 to 5.1 or higher, the server recognizes names that have non-ASCII characters and adds a #mysql50# prefix to them.

This happens because the data directory structure has changed on MySQL 5.1 and you need to upgrade the directory structure for the affected database(s).

This is done by running the mysql_upgrade script which will examine all tables in all databases for incompatibilities with the current version of MySQL Server. It also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

The mysql_upgrade script executes the following commands:

mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables
mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names

However, prior to MySQL 5.1.31, mysql_upgrade does not run the third command, which is necessary to re-encode database or table names that contain non-alphanumeric characters and remove the #mysql50# prefix. If you have such database or table names, execute the third command manually after executing mysql_upgrade or run the following query:

mysql > ALTER DATABASE `#mysql50#database` UPGRADE DATA DIRECTORY NAME;

http://dev.mysql.com/doc/refman/5.1/en/upgrading.html



MySQL Replication

February 8th, 2009 | Posted in Blog, Linux, MySQL | No Comments

MySQL database replication allows you to have an exact copy of a database on another server which allows you to setup an application to read from, i.e. auth.

All changes to the “master” server is replicated across to the “slave” server instantaneously. This is not a backup technique… any accidental DELETE queries on the master will well… see for yourself. Replication can however help against hardware failure.

On the master server, edit my.cnf and insert the following under the [mysqld] section:

##################################################
##  REPLICATION

log-bin
binlog-do-db=pdns
binlog-ignore-db=mysql
binlog-ignore-db=test

server-id=1

##################################################

The above will cause the database pdns to be replicated to our slave server and the mysql and test databases will be ignored. If you don’t need this, you can safely remove those lines. Additionally, you can specify to which file mysql should log the binary data. To do that, edit the file and set the log-bin directive to:

log-bin = /var/log/master-mysql-bin.log

Save the file and restart MySQL

/etc/init.d/mysqld restart

Next, connect to the MySQL CLI and add a user to be used for replication. The query below will do just fine for this.

GRANT REPLICATION SLAVE on *.* to 'replication'@172.16.0.30 identified by 'mypassword';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;

Take note of of the file name and the log position, you will need to enter this information on the slave later on.

Next you need to grab the data from the master and import it on the slave system. Personally I prefer to just do a dump and import it at the slave, but there are other ways to accomplish this.

mysqldump -u root -p -e  pdns > pdns080209.sql

On the slave system, connect to the MySQL CLI and create the database

CREATE DATABASE pdns;

Next, setup the slave settings in my.cnf

##################################################
##  REPLICATION

server-id=2

master-host = 172.16.0.29
master-user = replication
master-password = mypassword
master-port = 3306

##################################################

Save and exit the file then import the dump you just created on the master

mysql pdns -u root -p < pdns080209.sql

Note! If the database is BIG, you will get errors, please refer to this post to get a workaround.

Once the database has been imported, restart the slave server, set the log file position and start the slave 

/etc/init.d/mysqld restart
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST=’172.16.0.29, MASTER_USER='replication', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='master-mysql-bin.001', MASTER_LOG_POS=73;
START SLAVE;

Next, ensure both Slave_IO_Running and Slave_SQL_Running are showing up as YES, and that’s it… your done.



Got a packet bigger than 'max_allowed_packet' bytes

December 14th, 2008 | Posted in MySQL | 2 Comments

ERROR 1153 (08S01) at line 3995: Got a packet bigger than ‘max_allowed_packet’ bytes

If you get above error while importing a large MySQL dump connect to MySQL on one terminal and set:

set global max_allowed_packet=1000000000;
set global net_buffer_length=1000000;

Open another terminal and import your dump:

mysql -p < large_db_dump


MySQL skip duplicate replication errors

December 14th, 2008 | Posted in MySQL | No Comments

MySQL replication will stop if an error occurs when running a query on the slave. The reason is so you can resolve the problem, thus keeping the data consistent with the master. You can skip those errors if you know those queries and why they are failing.

Skip one query

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Skip all duplicate errors

edit my.cnf and add:

slave-skip-errors = 1062

You can skip all types of errors using the same method abobe, seperating each error code you wish to skip with a comma.

http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html




Categories