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怎么才算合理呢?
1 查看当前mysql最大连接数
show global status like ‘Max_used_connections’;
2 设置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的问题.
解决方法:
1 使用正确的环境编译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;
标签云
.htaccess 301 2010 Android apache cache cacti CSS date ddos discuz django fastcgi freebsd git google http iftop linux macos mysql nginx njava php pr python sed seo snmp ssh ubuntu ubuntu10.04 wordpress xdebug 优化 密码 文件 时区 用户 登录 监控 缓存 网站 脚本 颜色
WP Cumulus Flash tag cloud by Roy Tanck and Luke Morton requires Flash Player 9 or better.