mysql
mysql 远程访问权限开启
mysql -u root -p123456; mysql GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; mysql flush privileges;
macos下php+mysql不能使用localhost连接的问题解决
因为localhost是通过socket方式来连接，而127.0.0.1则是走的TCP协议。所以需要重新设置php-mysql的配置文件
$echo "show variables" | mysql | grep "socket" socket /opt/local/var/run/mysql5/mysqld.sock $vi /opt/local/var/db/php5/mysql.ini ; Do not edit this file; it is automatically generated by MacPorts. ; Any changes you make will be lost if you upgrade or uninstall php5-mysql. ; To configure PHP, edit /opt/local/etc/php5/php.ini. extension=mysql.so extension=mysqli.so extension=pdo_mysql.so mysql.default_socket= /opt/local/var/run/mysql5/mysqld.sock mysqli.default_socket= /opt/local/var/run/mysql5/mysqld.sock pdo_mysql.default_socket= /opt/local/var/run/mysql5/mysqld.sock
设置mysql.default_socket，mysqli.default_socket，pdo_mysql.default_socket
分析Mysql慢日志log_slow_queries
1 打开log_slow_queries
$sudo vi /etc/mysql/my.cnf log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 5 log-queries-not-using-indexes
2使用工具分析mysql slow日志
myprofi.php
3 mysql调优工具
tuning-primer.sh
使用persona for cacti插件监控mysql
需要mysql调优，安装了一个cacti监控插件 Percona Monitoring Plugins ，
官方地址：http://www.percona.com/software/percona-monitoring-plugins/
1 下载 percona-monitoring-plugins-1.0.0.tar.gz
2 上传 ss_get_mysql_stats.php 到cacti的scripts目录/usr/share/cacti/site/scripts
3 进入cacti的web页面import 数据模版cacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.0.0.xml
4 设置ss_get_mysql_stats.php里的mysql账号链接
5 Percona Monitoring Plugins参考文档：http://form.percona.com/rs/percona/images/Percona_Monitoring_Plugins_100_Operations_Manual.pdf
合理计算mysql最大连接数max_connections
mysql经常出现MySQL: ERROR 1040: Too many connections，需要重新设置max_connections怎么才算合理呢？
１ 查看当前mysql最大连接数
show global status like ‘Max_used_connections’;
２ 设置max_connections值:Max_used_connections/max_connections=80%
phpmyadmin链接mysql错误#2002错误解决
1 检查php链接mysql是否正常
$db = mysql_connect("127.0.0.1:3306", "root", "xxxxxxxx");
if (!$db) {
die('Could not connect' . mysql_error());
}
echo 'Connected successfully
';
2 如果正常修改phpmyadmin配置文件
FROM
$cfg[‘Servers’][$i][‘host’] = ‘localhost’;
TO
$cfg[‘Servers’][$i][‘host’] = ‘127.0.0.1’;
Python连接MySQLdb的mach-o, but wrong architecture错误解决
错误提示
/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/MySQL_python-1.2.3-py2.6-macosx-10.6-x86_64.egg/_mysql.so: mach-o, but wrong architecture
这个问题是32bit和64bit的问题.
解决方法:
１ 使用正确的环境编译MySQL-python-1.2.3.tar.gz,并安装
设置.bash_profile
PATH="/usr/local/mysql/bin:${PATH}"
export PATH
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib/
export VERSIONER_PYTHON_PREFER_64_BIT=yes
export VERSIONER_PYTHON_PREFER_32_BIT=no
2 编译安装MySQL-python-1.2.3.tar.gz
ARCHFLAGS="-arch x86_64" python setup.py build#64bit使用这个 ARCHFLAGS="-arch i386" python setup.py build＃32bit使用这个 sudo python setup.py install
3 开发环境IDE里设置DYLD_LIBRARY_PATH
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib/
4 检查
#python >>import _mysql
MacOs修改MySQL的默认编码设置
my.ini文件位置
$ cd /opt/local/etc/mysql5/ $ sudo cp /opt/local/share/mysql5/mysql/my-small.cnf my.cnf $ sudo vi my.cnf [mysqld] ... collation_server = utf8_general_ci character_set_server = utf8
搞定,MadOs各种版本的mysql的下载地址是
http://dev.mysql.com/downloads/mysql/
Mysqldump命令errno:24的解决
经常是用mysqldump命令备份mysql数据库，突然遇到这个问题，解决之
njava@njava:~/sql$ mysqldump -unjava -pnjava --default-character-set=gbk njava>njava.sql mysqldump: Got error: 1016: Can't open file: './njava/db_xxyou.frm' (errno: 24) when using LOCK TABLES
是用参数
--lock-tables=false
来解决这个问题
njava@njava:~/sql$ mysqldump -unjava -pnjava --default-character-set=gbk --lock-tables=false njava>njava.sql njava@njava:~/sql$
MySQL数据库命令行修改root密码的几种方法
方法1： 用SET PASSWORD命令
mysql -u root
mysql> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(’newpass’);
方法2：用mysqladmin
mysqladmin -u root password “newpass”
如果root已经设置过密码，采用如下方法
mysqladmin -u root password oldpass “newpass”
方法3： 用UPDATE直接编辑user表
mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD(’newpass’) WHERE user = ‘root’;
mysql> FLUSH PRIVILEGES;
在丢失root密码的时候，可以这样
mysqld_safe –skip-grant-tables&
mysql -u root mysql
mysql> UPDATE user SET password=PASSWORD(”new password”) WHERE user=’root’;
mysql> FLUSH PRIVILEGES;