altmannmarcelo
  • Member for 8 years, 11 months
  • Last seen more than 6 years ago
MySQL replication: most important config parameters for performance on slave server?
Accepted answer
9 votes

If your concern is about performance and you don't care about losing a small chunck of data(usually 1 second) in case of server crashes, I would change the follow variables: innodb_buffer_pool_size - ...

View answer
Issue on mysqldump in MySQL5.6
2 votes

Some information can be found in SET Syntax and this bug. The SET statement assigns values to different types of variables that affect the operation of the server or your client. Older versions of ...

View answer
Issue after moving the ib_logfile1 and ib_logfile0 files
2 votes

There is a small problem in your approach, if I understood correctly, you changed the log file size, and restarted MySQL with innodb_fast_shutdown = 0. The problem here is that you still have some ...

View answer
MySQL User with Host set to a Range of IP Addresses
17 votes

you can use the grant assign to a netmask like: GRANT ALL PRIVILEGES ON database.* TO 'user'@'81.10.20.1/255.255.255.240'; It will give you access from 81.10.20.1 -> 81.10.20.14, just change the ...

View answer
Will deleting data in master automatically delete data in slave?
Accepted answer
2 votes

Yes, it will automatic apply the delete on the slave, with some restrictions: If you have replicate-do-db=db_name enabled, it will automatically filter out the statements to the given database names ...

View answer
Is there any way to get the optimization time in MySQL?
Accepted answer
4 votes

yes, you can use profiling, for an example query SELECT * FROM Country LIMIT 5; you will need to do the follow: mysql> SET profiling = 1; mysql> SELECT * FROM Country LIMIT 5; . . . mysql> ...

View answer
Errors while creating multiple mysql-5.5 instances
1 votes

Looking in the error log you can see 130513 11:22:24 [ERROR] /backup/mysql-cbt/bin/mysqld: unknown option '--safe- show- database' 130513 11:22:24 [ERROR] Aborting 130513 11:22:24 ...

View answer
datetime vs timestamp in mysql 5.5
Accepted answer
2 votes

Like @a_horse_with_no_name told on his comment, the differences are documented in here, but here is some information: Size: datetime - uses 8 bytes for each field timestamp - uses 4 bytes for each ...

View answer
mysql replication delay very serious
1 votes

I wrote about something similar on MySQL replication: most important config parameters for performance on slave server? Basically you should focus on the follow variables on slave (see the other post ...

View answer
Does pt-table-checksum point out rows that are different between Master and Slave?
3 votes

By default, pt-table-checksum displays as result all tables even if there is no error or diffs. If you want to display just errors and diffs you can add the --quiet to the invocation of pt-table-...

View answer
How to create a mysql stored procedure through linux terminal
5 votes

You need to change the delimiter to create a procedure, otherwise MySQL will try to commit your query on select 'hello pro'; Change your procedure to: DELIMITER // create procedure test_pro() begin ...

View answer
How to avoid lagging when enabling log-slave-updates?
2 votes

As your main concern is to have an incremental backup solution, you can change the follow variables: Changes: innodb_max_dirty_pages_pct - set it to 75, then innoDB will cache some changes and flush ...

View answer
"freeing items" taking forever on inserts, updates and deletes
1 votes

I had similar problem with MySQL 5.1, you can see in here Innodb Slow queries since convert from MyISAM before start, have a look on This post, it will help you to understand how to configure your ...

View answer
What is the size of currently-existing tmp tables in innodb?
Accepted answer
1 votes

OPTION 1: To find the size of this table you can use the follow query: SELECT DATA_LENGTH+INDEX_LENGTH AStotalTable, TABLE_ROWS from information_schema.TABLES WHERE TABLE_SCHEMA = 'DB_NAME' AND ...

View answer
mysql replica ignoring server-id
Accepted answer
0 votes

As we suspected, you are making the changes on 4306 and connecting on default port which is the server running on 3306. use these : mysql -u user -p --port 4306

View answer
Database migration, how to
0 votes

You basically need to keep track the new and old id from mo_sms load all DB2.mo_sms that you want to copy for each row in this result set store the actual DB2.mo_sms id save this row on DB1.mo_sms ...

View answer
Live backup without lock
1 votes

If you want to have a full backup without lock the live database, I will suggest that you start to replicate all information from master server into a slave, and take a full backup right from slave, ...

View answer
How do I stop a query from running if the row is locked?
Accepted answer
0 votes

you can use GET_LOCK() to avoid the query to run if other query is running on the same lock name. you will basically do: SELECT IS_FREE_LOCK('maintenance'); #IF RESULT IS 1 SELECT GET_LOCK('...

View answer
Reinstall MySql but keep database tables and data
Accepted answer
2 votes

If the database files are accessible, you can follow these steps: mv /var/lib/mysql /var/lib/mysql.bkp sudo apt-get install mysql-server sudo /etc/init.d/mysql stop mv /var/lib/mysql.bkp /var/lib/...

View answer
How to search for a name in two columns in MySQL?
8 votes

I suspect that Hamed is the first name and Haddadi is the last name, you can do it in 3 ways; Solution 1: Split the query in name and last name: SELECT * FROM nbaplayermaster WHERE firstname LIKE ...

View answer
MySQL performance impact of increasing innodb_buffer_pool_size
4 votes

Yes there is impacts(better performance), you should set your innodb_buffer_pool_size to 70-80% on a dedicated machine. I would indicate this post http://www.mysqlperformanceblog.com/2007/11/03/...

View answer
MySQL - What exactly does smallint(2) permit?
0 votes

It's used just for display AND when zerofill is enabled, it will basically add left zeros if you data has less digits than specified. in your case, it will add 0's if your number has less then 2 ...

View answer
How to optimize a log process in MySQL?
1 votes

You can use a partition table, you can split the table using a hash on user_id, then you can create as many partitions you want(you can add partitions later on if you want). see: http://dev.mysql.com/...

View answer
Tinyint structure is confusing
1 votes

It's just used when zerofill is activated, see bellow: mysql> DESCRIBE zero_fill; +-------+------------------------------+------+-----+---------+-------+ | Field | Type | ...

View answer
Slave SQL thread got hanged
4 votes

Look into the master bin log to see which query is logged on position 32820355 of file binlog.001403 mysqlbinlog --start-position=32820355 binlog.001403 see if it helps to find what is causing the ...

View answer
MySQL replication between VPS and shared host
1 votes

if you have MySQL root access on the shared host, there is a change that it works, just make sure that you: Use the VPS as master Enable the shared host ip to connect on vps host run a SELECT @@...

View answer
MySQL Slaves lag behind master
1 votes

The Problem This problem normally happens because on master, you allow a poll of connections(threads) which run commands at same time (max_connections), but on the slave (on versions before 5.6) you ...

View answer
Replication on MySQL server
1 votes

If your error is ERROR 1129 (HY000): Host 'serverb' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' you can run a FLUSH HOSTS on master, it will flush the list ...

View answer
How does mysqldump take place when there are more than one database?
0 votes

you can use --lock-tables to lock all tables in the current dumped database or --lock-all-tables which will lock all tables in all databases. if you don't want to lock the tables at all, you can use ...

View answer