mysql
install on osx
brew install mysql
brew services start mysql
mysql -u root # test
# now use Sequal Ace
install on linux
sudo apt-get install mysql-server
An error occurred when reading the file ... Autodetect - Unicode (UTF-8)
solution "select Western (Mac OS Roman) as the encoding format for the file to import without issue." source: https://dba.stackexchange.com/questions/111549/cannot-locally-import-utf-8-encoded-sql-database-on-mac
sudo mysql.server start
sudo mysql.server start
mysql -uroot -p
#Enter password: (your local mysql password)
OSX gui client
Sequal Ace (AppStore) vs Sequal Pro
install (without password)
brew install mysql
brew services start mysql
mysql -uroot
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newrootpassword';
problem with secure-file-priv
- Look for "my.cnf" file which is located at /usr/local/etc
- add secure-file-priv = "" into my.cnf
- restart mysql using /usr/local/opt/mysql/support-files/mysql.server restart
ERROR 1698 (28000): Access denied for user 'rick'@'localhost'
mysql> GRANT ALL ON . to rick@localhost IDENTIFIED BY 'password';
set mysql for root from '' to something on OSX with XAMPP
/Applications/XAMPP/bin/mysqladmin --user=root password "something"
ERROR 1045 (28000): Access denied for user...
mysql -u root -p
export table to CSV file
SELECT * FROM uren INTO OUTFILE '~/Documents/backup/rick.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '
';
make backup of SQL database
mysqldump -uroot db_name > backup.sql
or
mysqldump -u YourUser -pUserPassword YourDatabaseName > wantedsqlfile.sql
Import SQL file
mysql -u DB_USER -p -h localhost DB_NAME < /tmp/dump.sql
MySQL data files on CentOS
/var/lib/mysql/
Location of MySQL files on Mac OSX installed with Homebrew
/usr/local/var/mysql
/usr/local/Cellar/mysql/5.7.12
MariaDB
installed with brew install mariadb
To connect:
mysql -uroot
To have launchd start mariadb at login:
ln -sfv /usr/local/opt/mariadb/*.plist ~/Library/LaunchAgents
Then to load mariadb now:
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist
Or, if you don't want/need launchctl, you can just run:
mysql.server start
copy mysql database from one server to another
see [[linux]]
mysql client for osx
format date from timestamp in mysql
SELECT DATE_FORMAT(FROM_UNIXTIME(beginDate),"%e-%m-%Y %H:%i")
[[http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format|more info]]
change mysql password
mysqladmin -u root password root
connect to mysql database
mysql -u rick -pXXXXXX
mysql -u globe4d_user -pXXXX
create database
create database databasename;
show grants
show grants;
ERROR 1044 (42000): Access denied for user
het zou kunnen dat je op de server met mysql probeert te verbinden met je 'gewone' user en dat die geen toegang heeft. Vaak wordt er voor je database een aparte user gebruikt bijvoorbeeld 'globe4d_user' met z'n eigen wachtwoord.
show databases
show databases;
use database
use db_name;
show tables
show tables;
show columns
show columns from uren;
problem
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
- try to start the mysql server:
mysql.server start
- ERROR! The server quit without updating PID file (/usr/local/var/mysql/rick.local.pid).''
install mysql using macports
problem: No such file or directory
- als php niet kan connecten met database controleer dan of php mysql uberhaupt kan vinden.
PHP Warning: mysqli::mysqli(): [2002] No such file or directory (trying to connect via unix:///var/mysql/mysql.sock) in
Hmm, [[http://www.mostafaberg.com/2011/08/fixing-the-unixvarmysqlmysql-sock-not-found-error-on-mamp/|dit]] misschien Misschien een conflict met de PHP van OSX zelf?