MySQL Notes
cannot find -lmysqlclient libmysqlclient
I've seen this is some searches leading to this site and thought I would comment on the cause in case someone needs to know how to fix this compile problem. This error simply means that the mysql-devel rpm (with redhat) is not installed. If you are running a different OS, the error means that you are missing mysql entirely (or, at least, the libmysqlclient.a file), or the compiler cannot find the libmysqlclient.a file in your search path. If it's a path problem, you can use the -L compile option to specify the correct lib directory: -L/usr/local/lib.
Import data into mysql database:
mysql -u user -p -D database < dump.sql
Export data from mysql database:
SELECT field FROM table WHERE something = 1 INTO OUTFILE "/path/to/your/output/file";<
Display columns in easy to read mode
To display columns as lists, add a '/G' at the end of the query
select * from Table \G;
Copy database to new database:
Find the data directory. On my last install, that was under /var/lib/mysql/data. Inside the directory, there will be a subdir with the database name. Simply copy that using 'cp -rp currentDB newDB'.
Erase binary log files (version 1):
The mysql directory sometimes fills up with binary logs. They are formatted as hostname-bin.001, hostname-bin.002, and so forth. To delete them, execute the following command (See the MySQL Docs for more information):
mysql> RESET MASTER
Erase binary log files on replicated servers:
On the master mysql server, check the current log file:
mysql> show master status; +-------------------+-----------+--------------+------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +-------------------+-----------+--------------+------------------+ | masterhost-bin.191 | 933521 | | | +-------------------+-----------+--------------+------------------+ 1 row in set (0.00 sec) mysql>
Take note of the File number. This one is 191. We need to check the slave too. If we just delete all the logs up to 191 and the slave server isn't at 191 yet, then we are going to break something. I'm not sure what because I haven't ever done it before and I don't want to.
Anyway, we need to check the slave too:
slave mysql> show slave status \G Master_Host: masterhost.gadgetwiz.com Master_User: masteruser Master_Port: 3306 Connect_retry: 60 Master_Log_File: masterhost-bin.191 Read_Master_Log_Pos: 934225 Relay_Log_File: slavehost-bin.161 Relay_Log_Pos: 954233 Relay_Master_Log_File: masterhost-bin.191 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: mysql Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 934225 Relay_log_space: 954233 1 row in set (0.00 sec)
Okay.. It's safe to purge the logs up to the 191 file
mysql> purge master logs to 'masterhost-bin.191';
Database Error after Copy: "Didn't find any fields in table"
This occurs when an innoDB table is copied. To fix it, change the database engine to MyISAM, copy the directory and alter the tables back to innodb:
mysql> use oldDatabase mysql> alter table table1 type=MyISAM; mysql> alter table table2 type=MyISAM; . . . mysql> alter table tablex type=MyISAM; (SHELL) # cp -rp /var/lib/mysql/data/oldDatabase /var/lib/mysql/newDatabase mysql> use oldDatabase mysql> alter table table1 type=InnoDB; mysql> alter table table2 type=InnoDB; . . . mysql> alter table tablex type=InnoDB; mysql> use newDatabase mysql> alter table table1 type=InnoDB; mysql> alter table table2 type=InnoDB; . . . mysql> alter table tablex type=InnoDB;
MySQL Access Privileges
Grants all privs to all databases
mysql> GRANT ALL PRIVILEGES ON *.* TO user@host;
Grant select and update privs on all databases
mysql> GRANT SELECT,UPDATE ON *.* TO user@host;
Grant all privs to mydatabase for user@host
mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO user@host;
Grant all privs to mydatabase for user@anywhere
mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO user@'%';
Assign a password
mysql> GRANT ALL PRIVILEGES ON *.* TO user@host identified by 'password';