DBA Sensation

March 11, 2009

MySQL disable safe update mode

Filed under: [MySQL] — Tags: , , — zhefeng @ 1:36 pm

when you update a table and in where clause the column name is not the primary key, you will get error said:

You are using safe update mode and you tried to update a table without…etc.”

It should be noted that even simple applications of UPDATE can conflict with the ‘safe mode’ setting of the mysql daemon. Many server admins default the MySQL daemon to ‘safe mode’.

If UPDATE gives an error like this:

“You are using safe update mode and you tried to update a table without…etc.”

…then it may be that your .cnf file must be edited to disable safemode. This worked for me. In order for the change in the .cnf file to take effect, you must have permission to restart mysqld in the server OS environment. There is a page in the online documentation that explains safe mode entitled ‘safe Server Startup Script’.

December 2, 2008

consolidated MySQL manual and troubleshooting notes

Filed under: [MySQL] — zhefeng @ 1:35 pm

This is a consolidated MySQL manual and troubleshooting notes

***1. Installation of the MySQL (version 5.0.45)
There are two methods for installing MySQL:
1). Linux native rpm package.
Linux always include mysql server as its standard system rpm package, so if you are lucky after you install the linux the mysql is always there. if it’s not there or the version is too low, you can use yum(rhn_register) to install/update it:
#yum install mysql
#yum update mysql

After installation, the default location of mysql will be under /usr/bin
The log file will be under: /var/log/mysqld.log
There is “mysql.server” or “mysqld” or “mysql” will be put into /etc/init.d for auto-start
Backgroud Process: mysqld_safe

2). Customized installation
For example: create user, make folder, copy installation file
#cd /databases
#mkdir MySQL
#groupadd mysql
#useradd -g mysql mysql
#gunzip –sysconfdir=/etc \
> –localstatedir=/usr/local/mysql/data \
> –with-charset=utf8 \

#./scripts/mysql_install_db –user=mysql
#mkdir /var/run/mysqld
#chown mysql:mysql /var/run/mysqld

#/databases/MySQL/5.0.45/bin/mysqld_safe &

After successful installtion you will get comments like below:
{To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/databases/MySQL/5.0.45/bin/mysqladmin -u root password ‘new-password’
/databases/MySQL/5.0.45/bin/mysqladmin -u root -h vanpgcmsdb03 password ‘new-password’
See the manual for more instructions.
You can start the MySQL daemon with:
cd /databases/MySQL/5.0.45 ; /databases/MySQL/5.0.45/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl}

***2. Start mysql
There are so many ways to start mysql (i am still confused by some)
1). run “mysqld_safe &”
This method run the process directly and you got the same process name if you use ps to check.
2). #service mysqld start (stop, status)
This one by starting the mysql daemon to start the process
3). #/etc/init.d/mysqld start or #/etc/init.d/mysql start (for earlier version)
This is same as service start.

***3.login with root and validate the installation
Remeber! the “root” user for mysql is not the unix “root” user indeed, but you can set them for the same passwd
And you can use “#mysqlcheck -V” to check the version
or “#mysqladmin version” to check running
or use perl script test if you have mysql-test folder, cd mysql-test ; perl mysql-test-run.pl
1). normally login with root is easy:
#mysql -u root –didn’t put passwd here because by default mysql root account password is blank

***4.trouble shooting
1). Error 1: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’
When connecting to a MySQL server located on the local system, the mysql client connects thorugh a local file called a socket instead of connecting to the localhost loopback address 127.0.0.1. For the mysql client, the default location of this socket file is /tmp/mysql.sock. However, for a variety of reasons, many MySQL installations place this socket file somewhere else like /var/lib/mysql/mysql.sock.

While it is possible to make this work by specifying the socket file directly in the mysql client command

mysql –socket=/var/lib/mysql/mysql.sock …

it is painful to type this in every time. If you must do so this way (because you don’t have permissions to the file in the solution below), you could create an alias in your shell to make this work (like alias mysql=”mysql –socket=/var/lib/mysql/mysql.sock” depending on your shell).

To make your life easier, you can make a simple change to the MySQL configuration file /etc/my.cnf that will permanently set the socket file used by the mysql client. After making a backup copy of /etc/my.cnf, open it in your favorite editor. The file is divided into sections such as:

[mysqld]
datadir=/usr/local/mysql/data
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/usr/local/mysql

[mysqld_safe]
log-error=/var/log/mysqld.log

If there is not currently a section called [client], add one at the bottom of the file and copy the socket= line under the [mysqld] section such as:

[client]
socket=/var/lib/mysql/mysql.sock

If there is already a [client] section in the my.cnf file, add or edit the socket line as appropriate. You won’t need to restart your server or any other processes. Subsequent uses of the mysql client will use the proper socket file.
If the mysql.socket file is not existed in any location, just restart mysqld and it will automatically generate one.

2).Error 2: Could not open connection ‘default’: 1045 Access denied for user ‘root’@’localhost’ or forgot root password
step1: edited the file /etc/my.cnf at the [mysqld] section
added this line: skip-grant-tables
step2: /etc/init.d/mysql.server restart
step3: mysql -u root could login successfully.
step4: reset the password
use gui admin to change the root password or:
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD(‘your_new_password’) where USER=’root’;
mysql> FLUSH PRIVILEGES;
step5: comment out the “skip-grant-tables” in /etc/my.cnf
step6: restart the instance/daemon without the –skip-grant-tables option.
You should be able to connect with your new password.
# mysql -u root -p
Enter password: your_new_password

3).Error 3: [host] is not allowed to connect to this MySQL Server
This is because the remote host hasn’t been grant the root access. To wide open the root remote managing, run:
mysql>use mysql
mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘yourpassword’;
mysql>flush privileges;

Create a free website or blog at WordPress.com.