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';