Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
tighar:mysql [2009/07/06 03:27]
moleski
tighar:mysql [2017/07/13 20:33] (current)
Line 1: Line 1:
 +====== setup ======
 +[[http://​www.mysql.com]]
 +
 +<​code>​
 +/​var/​lib/​mysql/ ​
 +/​var/​run/​mysqld/​
 +
 +/​usr/​bin/​mysqld_safe ​
 +/​usr/​libexec/​mysqld ​
 +
 +--basedir=/​usr ​
 +--datadir=/​var/​lib/​mysql ​
 +--log-error=/​var/​log/​mysqld.log ​
 +--socket=/​var/​lib/​mysql/​mysql.sock ​
 +--pid-file=/​var/​run/​mysqld/​mysqld.pid ​
 +--user=mysql ​
 +port 3306
 +mysql -S/​var/​lib/​mysql/​mysql.sock -ptest
 +</​code>​
 +
 +====== MYSQL commands ======
 +
 +<​code>​
 +service mysqld restart
 +service mysqld status -- returns Process ID
 +find / -name mysql.sock
 +ls -l /var/run
 +netstat -a | grep mysqld
 +cat /​root/​.mysql_history|more
 +
 +/​usr/​libexec/​mysqld --skip-grant-tables --user=mysql ​
 + --skip-grant-tables ​ -- start demon; supposed to allow universal access
 +
 +******* THIS WORKED! **************
 +/​usr/​bin/​mysqld_safe --user=mysql --socket=/​var/​lib/​mysql/​mysql.sock --pid-file=/​var/​run/​mysqld/​mysqld.pid --datadir=/​var/​lib/​mysql --skip-grant-tables --skip-networking &
 + This lets me connect to mysql.
 +
 +/​usr/​bin/​mysqld_safe --user=mysql --socket=/​var/​lib/​mysql/​mysql.sock --pid-file=/​var/​run/​mysqld/​mysqld.pid --datadir=/​var/​lib/​mysql --skip-networking &
 +
 +/​usr/​bin/​mysqld_safe --user=mysql --skip-name-resolve --skip-host-cache &
 +/​usr/​bin/​mysqld_safe --debug=d,​general,​query &
 +
 + /​usr/​libexec/​mysqld --skip-name-resolve &
 + 
 +mysql> update user set password=PASSWORD("​sitepassword"​) where User='​root';​
 +mysql> SET PASSWORD FOR root@localhost=PASSWORD('​sitepassword'​);​
 +
 +mysqladmin -u root password sitepassword
 +mysqladmin --no-defaults -u root version
 +
 +mysql -u root --password='​sitepassword' ​
 +mysql -uroot -psitepassword
 +
 +
 +*** mysql_install_db --user=mysql
 +  This may have been stupid. ​ user=root might
 +  have been smarter. ​ I think this is where a lot
 +  of my troubles came from.  Maybe.
 +</​code>​
 +
 +====== mysqladmin ======
 +<​code>​
 +/​usr/​bin/​mysqladmin -u root password '​sitepassword'​
 +/​usr/​bin/​mysqladmin --socket=/​var/​lib/​mysql/​mysql.sock -u root password '​sitepassword'​
 +/​usr/​bin/​mysqladmin -u root -h tighar password '​sitepassword'​
 +/​usr/​bin/​mysqladmin -u root -h 138.92.13.17 password '​sitepassword'​
 +</​code>​
 +
 +Alternatively you can run:
 +
 +     /​usr/​bin/​mysql_secure_installation
 +
 +which will also give you the option of removing the test databases and anonymous user created by default. ​ This is strongly recommended for production servers.
 +
 +====== Setting root password inside mysql ======
 +<​code>​
 +UPDATE mysql.user SET Password=PASSWORD('​sitepassword'​) WHERE User='​root';​
 +FLUSH PRIVILEGES;
 +
 +SET PASSWORD FOR '​root'​@'​localhost'​ = PASSWORD('​sitepassword'​); ​
 +
 +MYSQL CREATE USER
 +-----------------
 +create user newuser identified by '​mpassword' ​
 +GRANT ALL PRIVILEGES ON *.* TO '​newuser'​@'​localhost'​ IDENTIFIED BY '​mpassword'​ WITH GRANT OPTION;
 +GRANT ALL PRIVILEGES ON *.* TO '​newuser'​@'​%'​ IDENTIFIED BY '​mpassword'​ WITH GRANT OPTION;
 +</​code>​
 +
 +====== mysql error 1045 ======
 +<​code>​
 +"​mysqladmin:​ connect to server at '​localhost'​ failed"​
 +"ERROR 1045 (28000): Access denied for user '​root'​@'​localhost'​ (using password: NO)"
 +"'​Can'​t connect to local MySQL server through socket '/​var/​run/​mysqld/​mysqld.sock'"​
 +"BHost '​localhost'​ is not allowed to connect to this MySQL serverConnection"​
 +</​code>​
 +
 +This is a puzzler. ​ I've solved it several times--and then caused it again. ​ "​I'​m not all done with stupid yet.  There'​s more where that came from."
 +
 +Sometimes I've had it when calling mysql from a terminal, other times with phpMyAdmin.
 +
 +  * make sure port 3306 is open
 +  * have right sock location set in configuration files
 +  * for phpMyAdmin, //use a browser that does not capitalize my name// (!).  This is a new one for me.  Firefox changes "​moleski"​ to "​Moleski"​ for me--so nice!  But the login form for phpMyAdmin is Case SensiTive.
 +  * make sure ownership and permissions are ok for [[tighar:​phpmyadmin]].
 +
 +The mysqld log gives some helpful information about what it thinks it is doing:
 +
 +<​code>​
 +090704 19:​01:​52 ​ InnoDB: Started; log sequence number 0 43655
 +090704 19:01:52 [Note] /​usr/​libexec/​mysqld:​ ready for connections.
 +Version: '​5.0.77' ​ socket: '/​var/​lib/​mysql/​mysql.sock' ​ port: 3306  Source distribution
 +090704 23:05:33 [Note] /​usr/​libexec/​mysqld:​ Normal shutdown
 +</​code>​
 +
 +phpmyadmin config.inc.php
 +<​code>​
 +$cfg['​Servers'​][$i]['​host'​] = '​localhost';​
 +$cfg['​Servers'​][$i]['​connect_type'​] = '​tcp';​
 +$cfg['​Servers'​][$i]['​socket'​] = '/​var/​lib/​mysql/​mysql.sock';​
 +$cfg['​Servers'​][$i]['​auth_type'​] = '​cookie';​
 +$cfg['​Servers'​][$i]['​extension'​] = '​mysqli';​
 +</​code>​
 +
 +====== mysqldump backup ======
 +
 +<​code>​
 +cd /​usr/​local/​mysql/​backup
 +
 +mysqldump --password= ... --add-drop-database --all-databases > 2010-06-11.txt
 +
 +mysqldump --password= ... --add-drop-database --all-databases --xml > file.xml</​code>​
  
 
 
Recent changes RSS feed Creative Commons License Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki