37
The slickest way to shutdown mysql when it does that is simply to run
mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown
Here is why:
The mysql service file (/etc/init.d/mysql) relies on the presence of the socket file. Historically speaking, going way back to MySQL 4.0, the socket file sometimes disappears inexplicably. This hampers a standard ...
22
Brent listed some invalid reasons for stopping the service, but there are valid reasons too:
Restart required by a service pack or other update
Certain configuration changes (e.g. service account change, hardware changes, instant file initialization, this list could go on for weeks)
In a cluster, a restart to force a failover or applying a rolling patch
...
19
Because they think there's a memory problem - SQL Server uses all of the memory available to it, up to its max memory setting (and even beyond.) Unknowing folks go into Task Manager, see SQL Server using a lot of memory, and think, "There must be a memory leak - I'll stop and restart SQL Server, and see what happens." Sure enough, that frees up a lot of ...
14
You don't have to be fancy/worried or scared when you are restarting sql server.
Just make sure that you dont have any long running transactions. Best is to restart sql server using console or shutdown command during a low/minimum activity period also called maintenance window to minimize impact on your business.
If you have any DR setup and you dont want ...
8
You can use the dbstart/dbshut scripts which come with an Oracle install. They are available under $ORACLE_HOME/bin.
After a fresh install you have to edit the /etc/oratab file:
# cat /etc/oratab
# format: $ORACLE_SID:$ORACLE_HOME:N|Y
my_sid:/home/juser/app/juser/product/11.2.0/dbhome_1:N
# sed -i 's/:N$/:Y/' /etc/oratab
# grep my_sid /etc/oratab
my_sid:/...
6
Think of it this way:
the database is the set of physical files, on disk. It's a completely passive "thing". It doesn't "do" anything on its own - it's just data.
the instance is the software/processes (with its memory) that manages the database. It's the active part. It's what clients connect to, it's what processes SQL, reads data, updates it, maintains ...
6
Yes, it's possible.
SQL> alter database close;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database dismount;
Database altered.
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
5
This is all detailed exhaustively on this page.
Start, Stop, Pause, Resume, Restart SQL Server Services
Being that your question specifically asks "are there any recommended by Microsoft" I'm inclined to think that this is counter-productive to have this discussion here. The article their details the process through
Using either
command line
Powershell,
...
5
It sounds as if your question is less about "how to" shut down MySQL and more about why yours is shutting down so slowly.
In my answer to a similar question, I offered some suggestions for a smooth restart, which help by reducing the amount of activity that has to happen after you request that MySQL begin the shutdown process.
If you are not a frequent ...
4
Linux's Viewpoint
You should run this Linux command
history | grep mysqladmin
This will let you see if anyone ran a shutdown from within the server. Note that this will not let you see remote mysqladmin shutdowns. Perhaps running tcpdump and locating the word mysqladmin or shutdown might help.
MySQL's Viewpoint
Shutdown commands do not exist from ...
4
Without seeing any additional information, here is my best guess why MySQL is trying to shutdown for a long time: I suspect mysqld no longer has a socket file.
About 1.5 years ago, I answered mysql restart issue after move database. I learned that mysqld depends on the presence of a socket file. If there is no socket, mysqld just draws lots of dots on the ...
3
I ran into this problem on a managed system. Took me a while to spot the very obvious cause, which is that the system as a whole had gone down for scheduled maintenance -- I guess because an updated kernel package was available.
So obvious!
The fix was of course to change the system configuration so that MySQL was automatically started when the system ...
3
Ideally you can shutdown using
/etc/init.d/mysqld stop
I cannot add comments so requesting to post what do you see in error log when you do
mysqladmin shutdown
About kill commands please note that mysqld_safe is wrapper script which will start mysqld upon killing... so you should kill mysqld_safe first followed by mysqld. ( not a suggested way though)....
3
It helps to understand what 'mount mode' means:
Before DB is mounted, instance is up (SGA and other memory structures are allocated but DB and its files etc have not been verified yet. In mount mode, the controlfile will be read, it will make sure all logfiles/datafiles etc are available and ready to go. This is essential maintenance. Then, after mounting, ...
3
Not exactly when it comes to shutting down and preventing DB corruption. MS SQL Server is a very mature product and the odds of causing a corruption issue by a simple 'shutdown' would be a edge scenario. You're much more likely to cause corruption by not running CHECK DB or having checksum validation set on your DB.
Perhaps having external tools ...
3
SHUTDOWN
MySQL 5.7 now features the SHUTDOWN command, which requiresthe SHUTDOWN privilege.
SHUTDOWN METHOD #1: Within mysql client
mysql> SHUTDOWN;
SHUTDOWN METHOD #2: From OS using mysql client
MYSQL_HOST=127.0.0.1
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -P3306 --protocol=tcp"
mysql ${...
3
The documentation tells you to send a signal to the master postgres process, or to let pg_ctl do this.
In SQL, you can extract the PID of the master process from pg_read_file('postmaster.pid'), but pg_cancel_backend() does not accept this PID.
However, you should be able to execute these commands with COPY (depending on what rights the postgres OS user has)...
3
You can set the service property to restart it (service properties --> recovery --> first / second / third failure and have a delay between each restarts e.g. 1 min). You should also, have the service set to automatic delayed start.
Alternatively, you can use powershell and schedule it in task manager to check if the service is running or not. If not ...
2
DISCLAIMER : Not a MacOS user
That is an alternative way. This shutdown method can be done in MacOS, Linux, Windows, any platform MySQL is supported in.
I actually prefer your mysqladmin method for a reason...
In Linux, I have seen the mysql.sock (the socket file) file just up and disappear without warning. The standard way to shutdown mysql in Linux is ...
2
It's probable MySQL is not purely locked up, but is doing cleanup activities (rollbacks, etc.) when shutting down. If you don't let it do all that when shutting down, often you'll have to wait when starting up.
Here's something to look at: Shutdown mysql in one terminal window while watching the error log (tail -f [yourerror.log]) in another terminal window....
2
When you issue service mysql stop, a lot more happens than just cutting off DB Connectivity. The link in the comment from @ethrbunny already explains what things happens.
I would like to focus on one particular aspect: The InnoDB Buffer Pool. InnoDB has to flush the InnoDB Buffer Pool's dirty pages. If you want to know how much, run this before shutdown:
...
2
If you would like to manually control this, here are some suggestions
Login to Window Command Line Shell as Administrator and run this
C:\> net stop mysql
When the prompt comes back, MySQL is down.
If you shutdown Windows, the only evidence for MySQL's shutdown would be in the error log.
EXAMPLE
On my laptop
I have MySQL 5.5.37 for Windows
It's ...
2
If you want to shutdown MySQL effectively, you need to run several things
@echo off
rem
rem Flush Everything and its Grandmother
rem
echo "Stopping MySQL Service"
"C:\host\MariaDB\bin\mysql.exe" -u root -prootpass -ANe"SET GLOBAL innodb_fast_shutdown=0; STOP SLAVE; FLUSH BINARY LOGS; FLUSH TABLES"
"C:\host\MariaDB\bin\mysqladmin.exe" -u root -prootpass ...
2
It seems like the obvious answer is that you can't stop a shutdown that is happening, but you can do either a shutdown immediate or shutdown abort from a different session if you did a shutdown normal. As long as you have enough online redo log groups and each group has a log that is large enough, then you can recover from either a shutdown immediate or ...
1
A valid reason is when there is other software running on the same server that needs some of the memory SQL server has, but that only runs a few times a month.
For example my wife (an accountant who wishes to know as little (and no less) about SQL server that is needed to do her job) has a SQL server based system used by 3 people including her to process ...
1
this is surely a resources or limits problem.
For the first check if you have RemoveIPC enabled (Man for login.conf
If it does not solve, review the Database Quick Installation Guide in the sections Configuring Kernel Parameters and Resource Limits and Creating Required Operating System Groups and Users
Regards
Giova
1
You may not be aware of this, but the MySQL service is designed to be restarted automatically.
There is a program called mysqld_safe. Its job is to do the following:
STEP 01 : start mysqld
STEP 02 : check return value of mysqld
STEP 03 : If return value is zero, mysqld_safe terminates normally
STEP 04 : mysqld is restarted due to abnormal termination of ...
1
Try stopping the mysql server, instead of killing the process:
sudo /etc/init.d/mysql stop
1
MySQL can run as a service and I hope that windows is stopping the services gracefully or you are likely to have more problems outside of MySQL. If you want to be careful, stop the service by hand. This should allow MySQL to flush all buffers before stopping. I would guess from your comment that you're getting corruption that you are using the MyISAM engine; ...
1
One perfect example why root access even for admins is bad. Maintaining a sudoers file and a centralized database of privileged users and groups(LDAP for example) would help solving problems like this.
One note: the wrongdoer might well be a cronjob. I'd check that.
Only top voted, non community-wiki answers of a minimum length are eligible
