MySQL
Submitted by sandip on Sat, 03/13/2010 - 02:11.
You can now get the latest PHP with mysqlnd (MySQL Native Driver) support via remis' yum repository as mentioned in PHP-5.3,-zts-and-mysqlnd. The blog mentions that this is enabld in php-zts. However, no php-pecl extension are available and neither are some of the extensions thread safe. So I went about rebuilding php package from source for mysqlnd support.
Download the source php rpm from http://rpms.famillecollet.com/SRPMS/ . Note, I have used php-5.3.2 which is the latest as of this writing.
Install and apply the below diff patch to the spec file via `patch -p0 < {new_patch_file}` where "new_patch_file" has the below contents:
--- php-5.3.2-remi.spec.orig 2010-03-11 23:07:04.000000000 -0600
+++ php-5.3.2-remi.spec 2010-03-11 23:36:03.000000000 -0600
@@ -24,6 +24,7 @@
%global phpversion 5.3.2
# Optional components; pass "--with mssql" etc to rpmbuild.
+%define with_mysqlnd %{?_with_mysqlnd:1}%{!?_with_mysqlnd:0}
%define with_oci8 %{?_with_oci8:1}%{!?_with_oci8:0}
%define with_ibase %{?_with_ibase:1}%{!?_with_ibase:0}
%if %{?rhel}%{?fedora} > 4
@@ -677,6 +678,11 @@
%if %{?fedora}%{?rhel:99} >= 10
--with-system-tzdata \
%endif
+%if %{with_mysqlnd}
+ --with-mysql=shared,mysqlnd \
+ --with-mysqli=shared,mysqlnd \
+ --with-pdo-mysql=shared,mysqlnd \
+%endif
$*
if test $? != 0; then
tail -500 config.log
@@ -704,8 +710,13 @@
--enable-dba=shared --with-db4=%{_prefix} \
--with-xmlrpc=shared \
--with-ldap=shared --with-ldap-sasl \
+%if %{with_mysqlnd}
+ --with-mysql=shared,mysqlnd \
+ --with-mysqli=shared,mysqlnd \
+%else
--with-mysql=shared,%{_prefix} \
--with-mysqli=shared,%{_bindir}/mysql_config \
+%endif
%ifarch x86_64
%{?_with_oci8:--with-oci8=shared,instantclient,%{_libdir}/oracle/%{oraclever}/client64/lib,%{oraclever}} \
%else
@@ -725,7 +736,11 @@
--enable-fastcgi \
--enable-pdo=shared \
--with-pdo-odbc=shared,unixODBC,%{_prefix} \
+%if %{with_mysqlnd}
+ --with-pdo-mysql=shared,mysqlnd \
+%else
--with-pdo-mysql=shared,%{_prefix} \
+%endif
--with-pdo-pgsql=shared,%{_prefix} \
--with-pdo-sqlite=shared,%{_prefix} \
--with-pdo-dblib=shared,%{_prefix} \
@@ -756,6 +771,16 @@
--with-recode=shared,%{_prefix}
popd
+%if %{with_mysqlnd}
+without_shared="--without-gd \
+ --disable-dom --disable-dba --without-unixODBC \
+ --disable-xmlreader --disable-xmlwriter \
+ --without-sqlite \
+ --disable-phar --disable-fileinfo \
+ --disable-json --without-pspell --disable-wddx \
+ --without-curl --disable-posix \
+ --disable-sysvmsg --disable-sysvshm --disable-sysvsem"
+%else
without_shared="--without-mysql --without-gd \
--disable-dom --disable-dba --without-unixODBC \
--disable-pdo --disable-xmlreader --disable-xmlwriter \
@@ -764,6 +789,7 @@
--disable-json --without-pspell --disable-wddx \
--without-curl --disable-posix \
--disable-sysvmsg --disable-sysvshm --disable-sysvsem"
+%endif
# Build Apache module, and the CLI SAPI, /usr/bin/php
pushd build-apache
Package with:
rpmbuild -bb --with mysqlnd --define "rhel 5" SPECS/php-5.3.2-remi.spec
To install via yum, change to the directory where rpms are located and recreate the repodata via:
createrepo .
Note: you may need to install some of the missing dependent devel packages. I used a combination of base, epel and remi repository to install the dependencies.
Submitted by sandip on Fri, 12/04/2009 - 17:13.
PhpMyAdmin Error:
#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)
There are various reasons for this error. Several things to check are:
Edit config.inc.php file, and specify '127.0.0.1' (instead of any other value like 'localhost) for the $cfg['Servers'][$i]['host'] setting as follows:
$cfg['Servers'][$i]['host'] = '127.0.0.1';
Check my.cnf file for the following entries:
[mysqld]
socket=/tmp/mysql.sock
old-passwords
[client]
socket=/tmp/mysql.sock
Only add the 'old-passwords' mentioned above if you are using an older version of PHP (PHP4).
Make sure that you do not have any older versions of MySQL installed that are conflicting.
Edit config.inc.php file, and specify the MySQL socket location (as seen in the above [mysqld] socket setting):
For example, if your MySQL socket is /tmp/mysql.sock, find the line in config.inc.php that has the $cfg['Servers'][$i]['socket'] setting and change it to:
$cfg['Servers'][$i]['socket'] = '/tmp/mysql.sock';
Set the connection mode to socket by changing $cfg['Servers'][$i]['connect_type'] to:
$cfg['Servers'][$i]['connect_type'] = 'socket';
If there is no such setting, simply add in the above line and re-try.
Add this entry to php.ini file (again changing the '/tmp/mysql.sock' to your actual socket location) and restart Apache:
mysql.default_socket = "/tmp/mysql.sock"
Edit the config.inc.php using the phpmyadmin /setup configuration page:
In the setup screen, select the end-of-line character to be '\r' if you have a Macintosh, or '\n' for a Linux machine.
Disable SELinux if you are using it, and re-try. If it works, SELinux is the issue.
Run the following at your mysql prompt (using the standard mysql client):
mysql> SELECT USER,HOST FROM mysql.user;
Next, identify the user your are using for phpMyAdmin, and then run:
mysql> SHOW GRANTS FOR 'user'@'host';
Ensure that this userid has appropriate access rights.
Submitted by NewMusicPromote on Fri, 09/25/2009 - 08:10.
Dear Anyone.
Hello i came across this website doing a search for My.Cnf tweeks or configurations for the SQL_Max_Connections and i was wondering some things. I have a Linux CentOS system with 2.5 gigs of Ram, Running Apache. and PHP. what would be the "proper" My.cnf file settings for a Music Website i keep getting a max connection overload on the network.
Matthew Nalett
New Music Promote
http://www.newmusicpromote.com
Here is my current My.Cnf settings.
Any Suggestions on how to optimize this for a Music Network?
[mysqld]
set-variable=local-infile=0
Submitted by jonitas on Wed, 05/06/2009 - 10:49.
Can anybody help me?
Basically my app is for an email marketing, so using a while statement its reads from a db (mysam) and make updates
Thank in advance!!
This is the info about my VPS (when typing )
Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz
Mem: 1206272 kB
OS: CentOS release 5.3 (Final)
Mysql: 5.0.45-log
This is my my.cnf:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
###The MySQL server
[mysqld]
set-variable=local-infile=0
Submitted by sandip on Tue, 05/22/2007 - 15:24.
Latest Compile with pdo drivers for mysql along with mod_security.
NOTE:
Remove the MySQL-shared rpm else openssl will not work.
# rpm -e MySQL-shared-5.0.20a-0.glibc23
Submitted by sandip on Thu, 03/30/2006 - 10:39.
Recently, I forgot the root password for MySQL and went about resetting it as below:
Stop mysqld and restart it with:
# service mysqld stop
# mysqld_safe --skip-grant-tables --user=root
Connect to the mysqld server with this command:
sql> mysql -u root
Issue the following statements in the mysql client:
mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
mysql> FLUSH PRIVILEGES;
Replace “newpassword
Submitted by sandip on Thu, 01/26/2006 - 11:10.
Here's a quick tip at sorting VARCHAR type data in mysql database with values in a column.
With the default sort, it would look something like below:
mysql> SELECT column FROM table_name ORDER BY column;
column
======
100
1000
10000
200
2000
20000
...
Now with "... ORDER BY column+0", I get it sorted right:
mysql> SELECT column FROM table_name ORDER BY column+0;
column
======
100
200
1000
2000
10000
20000
...
This is a quick fix instead of sorting to CAST operator.
Submitted by sandip on Mon, 01/31/2005 - 20:50.
Had to do some fine tuning of MySQL 4.1.9 and here is what my.cnf file looks like for a 2GHz machine with 1GB of memory.
[mysqld]
socket=/path/to/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb
# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
# max_connections=500
# Reduced to 200 as memory will not be enough for 500 connections.
# memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections
Submitted by sandip on Mon, 08/30/2004 - 22:33.
# Create User
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'];
# Create Database
$ mysqladmin -u <username> -p create <nameOfDatabase>
# Drop/Delete Database
$ mysqladmin -u <username> -p drop <nameOfDatabase>
# Check Process List
$ mysqladmin -u root -p proc
# Check Status at 5 seconds interval
$ mysqladmin -u root -p -i 5 status
# Dump Database
$ mysqldump --opt -u <username> -h <hostname> <nameOfDatabase> -p > /path/to/file
$ mysqldump --opt -u <username> -h <hostname> --all-databases -p > /path/to/file
# Import Database
$ mysql -h <host> -u <username> <nameOfDatabase> -p < /path/to/file
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON <dbname>.* TO <dbuser@localhost> [IDENTIFIED BY '<password>'];
REVOKE ALL ON <dbname> FROM <dbuser@localhost>;
CREATE DATABASE <dbname>;
DROP DATABASE <dbname>;
DROP TABLE <tablename1[, table2, table3...]>;
# To activate new permissions
FLUSH PRIVILEGES;
USE <nameOfDatabase>;
SHOW DATABASES;
# show tables begining with the prefix
SHOW TABLES LIKE 'prefix%';
SELECT * FROM <nameOfTable>;
DESCRIBE <nameOfTable>;
INSERT INTO <table> <username, password, name1, name2, ...> VALUES ('user', password('pass'), 'value1', 'value2' ...);
CREATE TABLE <newtable> AS SELECT DISTINCT <field> FROM <oldtable>;
INSERT INTO <database.table> SELECT * FROM <database.table> WHERE <field> = <value>;
ALTER TABLE <tableOldName> RENAME <tableNewName>;
UPDATE <tableName> SET <field1> = <newValue> [WHERE <field2> = <currentValue>];
|
